Aggregate Functions In Where Clause

Aug 29, 2005

I was wondering if anyone can help me: I have for example Pupils that are being entered into a table (called log). A query then counts the number of entries for each pupil I then want it ONLY to show the pupils that have been entered in 10 times on todays date (using DATE()).
I know this doesnt work but this is what I want to do:

SELECT log.pupilNo, log.Forename, log.Surname, Count(log.pupilNo) AS Demerits
FROM log, Tally
WHERE (log.Date)=Date() AND COUNT(log.pupilNo)>9
GROUP BY log.pupilNo, log.Forename, log.Surname, log.pupilNo;

Apparenty you cannot have an Aggregate function in a where clause.
Does anyone know a solution, any help would be appreciated
Cheers
Bikeboardsurf

View Replies


ADVERTISEMENT

Add Calculations To Existing Aggregate Functions

Oct 16, 2006

Hi,

Is it possible to add a calcualted field to a query that already uses 'sum'?
I want to take the value returned by sum and add the value of another field to it.

Any help will be very much appreciated.
B

View 12 Replies View Related

DLookup/Aggregate Domain Functions

Jun 7, 2007

Is there any way to lookup items using DLookup or other functions that will return an array of values not just the first value found. Example: I have three employees in a complaint query. I am using Dlookup to identify them on my employee form. When I get to the same employee/employee id on my employees form, I have a label that flashes red to flag me that this employee appears on that complaint query. The problem that I am having is that it only flashes for the first employee found in the query, and the label only flashes for that one person. I would like the label to flash for the other two employees because the function should see that they are in the query results as well. Help please?!!!

View 2 Replies View Related

Need Help: Memo Field And Aggregate Functions

Jun 21, 2005

I know SQL well enough, but I cant seem to get my query to work. Basically, I am trying to display a series of posts, with the number of comments on the side.

I have two tables, Entries and Comments, which look like this:
Entries
---------
IDTitleContent (memo field)
1CatsSomething about cats here
2DogsSomething else about dogs here
3RodentsMy pet rat runs in a wheel
etc.


Comments
--------
IDPostIDComments
11My cat's name is mittens
21I taught my cat how to throw a curve ball
33Rats like squeak toys
42Nobody likes dogs
53Bunnies make good pets
61Cats are witches in disguise
Comments.PostID corresponds to Entries.ID on a many-to-one relationship.

I want to run a query that will join the number of comments (which is a Count of each row in the Comments table where Comments.PostID = Entries.ID) to the existing table, so that it will look like this:

Entries (with #ofcomments joined)
----------
IDTitleContent (memo field)#ofcomments
1CatsSomething about cats here3
2DogsSomething else about dogs here1
3RodentsMy pet rat runs in a wheel2
etc.

Usually, this would be a really simple SQL statement, but as I am using a Memo field, I am getting a "cannot use Memo field in aggregate function" error.

I'm not sure what to do, any help would be appreciated :)

View 4 Replies View Related

Queries :: Aggregate Functions With Conditions?

Mar 22, 2013

I have the following query. I got an error when I ran it.

Code:
SELECT COUNT([encounter_number] WHERE status = 'Death')/ COUNT([encounter_number]) AS Death Ratio, tbl_test.facility_type AS Type,
FROM tbl_test
GROUP BY tbl_test.facility_type;

I think the problem is that first WHERE clause inside.

View 2 Replies View Related

Domain Aggregate Functions In Query Expressions

Apr 3, 2007

Alright, I've got all the pieces to this puzzle, I just can't fit them together properly. I have two tables, tblTickets and tblTicketNotes. They are in a one-to-many relationship, there can be multiple Notes attached to a single ticket. I am trying to set up a query that will pull the first (earliest by date) note for each individual Ticket. Here are the fields from each table that would be of concern here:

tbTickets
TicketID - Autonumber - Primary Key
Issue - Text - I will be using this later as a criteria to limit with, but not neccessary

tblTicketNotes
NoteID - Autonumber - Primary Key
TicketID - Long Integer - This is the foreign key of the relationship
DateStamp - Date/Time - This is the Note Date, I only want the first (earliest date) one
Content - Memo -This is the note information I want

So all I want is the Content of the first/earliest Note for each individual Ticket. I know this should be fairly easy, but I am at a loss.

View 2 Replies View Related

Forms :: Syntax For Nested Aggregate Functions?

Feb 4, 2014

I have a problem with a nested arrangement of Right, DLookup and DMax functions.

The function is for a default value in a text control

=Right(DLookUp("[SampleNumber]","tbldat14A_MasterSampleList","[SampleID]=" & DMax("[SampleID]","tbldat14A_MasterSampleList"))+1000001,6)

I am trying to retrieve the last record from a field SampleNumber which is alphanumeric (e.g. "AK005434") and then add a 1 to it as the next SampleNumber. I had previously used a default value in the txtSampleNumber control as

="AK" & Right(1000000+DMax("[Clip]","[tbldat14A_MasterSampleList]")+1,6)

where [Clip] was a field I have calculated in the table to chop off the preceding characters. Adding 1,000,000 and taking the 6 right hand values and concatenating with "AK" gave me the answer, though it is a but primitive.

This all worked until the SampleNumber value got out of order and blocks of SampleNumber values came in that were then followed by blocks of numbers with lower values (say AK005001-AK005050 followed by AK002001-AK002050).

Now I figure if I just recall the latest entry by DLookup and criteria of DMax on the SampleID (Autonumber Primary key) I could get at the value. I have done this to some success using default values in a series of unbound controls like

=DLookUp("[SampleNumber]","tbldat14A_MasterSampleList","[SampleID]=" & DMax("[SampleID]","tbldat14A_MasterSampleList"))

to get the SampleNumber I require, then a Right function to trim in another unbound box and then use that last unbound box as the default value for the txtSampleNumber control that is the entry for the data table. However, the unbound control box is only valid for the first record and does not update. So, I added a macro that closes the form and reopens it. This all works but is a bit agricultural. I would like to do a single nested function to the default value of the txtSampleNumber control box. Is it possible to nest Right, DLoopkup and DMax into one statement?

View 14 Replies View Related

Queries :: Putting Aggregate Inside A Where Clause

Oct 11, 2014

I need to change the below to a where clause to fit inside a union query that is just where clauses.

tblNEWNONTODATA.DateOfVisit) AS FirstOfDateOfVisit
FROM tblNEWNONTODATA
GROUP BY tblNEWNONTODATA.EVX, tblNEWNONTODATA.TCGDecision
HAVING (((tblNEWNONTODATA.TCGDecision)="Adopted" Or (tblNEWNONTODATA.TCGDecision)="Hot Tasked") AND ((First(tblNEWNONTODATA.DateOfVisit))>=#10/1/2014#));

View 2 Replies View Related

Troubles With Aggregate Functions In Queries And Null Values

Jun 26, 2007

I'm having a problem with queries, and I can't seem to find a solution in books - I looked through about ten of them and none of them addressed the problem. This may be because it has a painfully obvious solution...

A little background:
I am designing a database for a debt-collection law firm. One of the functions it must have is to keep track of various different sorts of financial transactions which can pertain to a given debtor (ie, a received payment, a cost expended, and a few other things).

The problem is that, in generating reports, I need to use queries to find several sums of only those transactions which fall into specific categories (for instance, to calculate the amount a debtor has paid against his balance, it needs to sum only those entries which are both linked to that debtor's ID number and whose type field reads "payment", and then subtract from that those entries whose type field reads "cost"). The problem is this: not all debtors may have "costs" entries, and when there are none, the report comes up blank with a single "#Error" written in the name field and nothing else present.

I believe the problem is that the Sum aggregate is returning a null value when the query finds nothing that meets the criteria. I have been unable to find a way around this; the Nz() and IIf() with IsNull() functions don't seem to be helping.

The query runs as intended when there are entries for every relevant type; however, it is undesired to have to enter a "payment" of $0, "cost" of $0 etc for every entry just so that this function works.

Is there anything I can do about this? Any input would be appreciated, as I'm fairly inexperienced with the use of Office Access. (If it matters, I am using Office 2003).

View 2 Replies View Related

Queries :: Refer To Aggregate Function In WHERE Clause (AC2007)

Dec 10, 2013

I have a SQL query to gather data from a number of tables (balances, accounts, currencies)

Quote:

SELECT [tblBalances].[BalanceDate], [tblAccounts].[AccountNumber], [tblCurrencies].[Ccy], [tblBalances].[Amount], ([tblBalances].[Amount]*[tblRates].[FXRate]) AS AmountUSD
FROM (([tblBalances] INNER JOIN [tblAccounts] ON [tblBalances].[AccountID]=[tblAccounts].[AccountID]) INNER JOIN [tblCurrencies] ON [tblBalances].[CcyID]=[tblCurrencies].[CcyID]) INNER JOIN [tblRates] ON ([tblBalances].[BalanceDate]=[tblRates].[RateDate]) AND ([tblBalances].[CcyID]=[tblRates].[CcyID])
WHERE BalanceDate = #12/10/2013#

How do I add 'AmountUSD' to the WHERE clause (such that I can only return records above or below a certain value, for example)

Along the lines of :

Quote:

WHERE BalanceDate = #12/10/2013# AND AmountUSD>1000

I know it's an issue with referring to aggregated functions in the WHERE clause and you're supposed to use HAVING instead

View 12 Replies View Related

Modules & VBA :: Using A Custom Aggregate Function With GROUP BY Clause

Dec 20, 2013

I am trying to calculate annual percentiles of a large set of data and I have only been successful at retrieving the percentile of the entire data set (and not by the grouping). See provided example database for code/query. Query1 is what I want to happen to make the Percentiles table.

View 14 Replies View Related

Domain Aggregate Functions To Count Occurrences Of Specific Value In Field

Jul 8, 2012

i need domain aggregate functions to count the occurrences of specific value in a field, and when it exceeds 1, display null. or 'group by' subqueries with count() to see if you should populate the field or not.

View 3 Replies View Related

2 Aggregate Function

Jun 16, 2005

Hi All,

I am using the following query to retrieve sum of gross total and nettotal :

SELECT BOOKINGNO, AGENT, CLIENT, DATE, ARRIVAL_DATE, NUMOFDAYS, GROSSTOTAL, TOTAL, SUM(GROSSTOTAL) AS SUMGROSS, SUM(TOTAL) AS SUMTOTAL FROM BOOKINGS
MONTH(ARRIVAL_DATE) = PICKMONTH
AND
YEAR(ARRIVAL_DATE) = PICKYEAR

where PICKMONTH and PICKYEAR are the pickup month and year of the travel.
The above query doesn't execute and give error as following

You tried to execute a query that does not include the specified expression 'ACH_REF' as part of an aggregate function.

Please help me

Jigs :confused:

View 7 Replies View Related

Text Sum Aggregate

Nov 21, 2005

Hi,

I have a tricky question. I have a table like this one:

ID ID2 Value
----------------
1 1 Apple
2 1 Pear
3 2 Apple
4 2 Banana
5 2 Pear
6 3 Apple

And I would like to get a query result like this one:

ID2 AggregateValue
-----------------------
1 Apple-Pear
2 Apple-Banana-Pear
3 Apple

In MySQL I would use the Concat() function with a group by (not tested tough). But in MSAccess?

Thanks in advance,

Massimo

View 1 Replies View Related

Using IIF With An Aggregate Function

Jun 26, 2006

Hi,
Can anyone please help me out with the IIF concept of MS-ACCESS.
I have a table with the following data:
Name Age Place Dept
AAA 13 Mumbai Accounting
BBB 12 Pune Banking
CCC 15 Delhi Finance
DDD 30 Mumbai Accounting
EEE 25 Delhi Finance

Now i need a query output like
Expected output:
---------------
Dept Total-count Mumbai Pune Delhi

Accounting 2 2 0 0
Banking 1 0 1 0
Finance 2 1 0 1
Now i m using the query:
*******************
SELECT [Dept], sum(test-table.dept) AS ['total count'], Sum(IIf([place]="Mumbai",1,0)) AS Mumbai, Sum(IIf([place]="Pune",1,0)) AS Pune, Sum(IIf([place]="Delhi",1,0)) AS Delhi
FROM [Test-Table]
GROUP BY [Dept];
*******************
and the output which i am getting is:
Dept Total-count Mumbai Pune Delhi

Accounting 2 0 0
Banking 0 1 0
Finance 1 0 1
can anyone let me know as to what change should be there in the query so that i get the correct values in Total-count field with the sum of horizontal counts?

Thanks,
SK.

View 4 Replies View Related

Help With Aggregate Query

Jul 17, 2007

Hello,

I have a table as such:

NAME RANK WIN_PERCENT DRAW_PERCENT

The table can have the same people in it multipe times, such as:

NAME LEVEL WIN_PERCENT DRAW_PERCENT

Bob 4 80% 4%
Bob 5 90% 5%
Bob 8 60% 3%

What I would like to do is return only the row in which Bob's Level is the Highest (MAX).

I wrote an aggregate query as such:

SELECT NAME, MAX(LEVEL) AS MAX_LEVEL, WIN_PERCENT, DRAW_PERCENT
FROM MYTable
GROUP BY NAME, WIN_PERCENT, DRAW_PERCENT

The problem is, as you'd expect, this query returns all rows, since I am grouping by WIN_PERCENT and DRAW_PERCENT as well.

Any ideas on what query to use to just return the row with the highest (MAX) LEVEL? i.e.

NAME LEVEL WIN_PERCENT DRAW_PERCENT

Bob 8 60% 3%

Thanks,

Scott

View 6 Replies View Related

Aggregate Value For Each Group By

Jul 7, 2015

I'm looking to return different average values for each GICS Sector using this query:

Code:
SELECT [GICS Sector], DAvg("GM","tbl0"," [GICS Sector] = [GICS Sector] AND GM Is Not Null") AS GmMedianByGicsSectorFROM tbl0
WHERE [GICS Sector] = [GICS Sector]
GROUP BY [GICS Sector];

However, I am getting the same value for each:

GICS Sector GmMedianByGicsSector
Consumer Discretionary 0.469556278179105
Consumer Staples 0.469556278179105
Energy 0.469556278179105
Financials 0.469556278179105

Health Care 0.469556278179105
Industrials 0.469556278179105
Information Technology 0.469556278179105
Materials 0.469556278179105
Telecommunication Services 0.469556278179105
Utilities 0.469556278179105

View 6 Replies View Related

Query With Aggregate Function

May 18, 2006

Hey guys, can anybody help
here is what I have:

Job
JobId-----PK
Name

Position
PositionId----PK
Name

Activity
ActivityId-----PK
Name

JobTask as you can see the relationship between Job and Task is M-M
JobId-----PK
TaskId-----PK

PositionTask as you can see the relationship between Position and Task is M-M
PositionId-----PK
TaskId-----PK

ActivityTask as you can see the relationship between Activity and Task is M-M
ActivityId-----PK
TaskId-----PK

Task
TaskId-----PK
TaskName

TaskCompetency as you can see the relationship between Task and Competency is M-M
TaskId-----PK
CompetencyId-----PK

Competency
CompetencyId-----PK
CompetencyName
Keyword
Priority


I have a query like this:

The user will input the JobId, PositionId, ActivityId.
I want to return all the TaskName ,CompetencyId, CompetencyName
Where the following:
The highest priority within the same keyword
Basically group by keyword then pick the highest priority I have done this:


SELECT Max(Competency.Priority), Competency. Keyword
FROM Competency INNER JOIN ((ActivityTask RIGHT JOIN (positionTask RIGHT JOIN (JobTask RIGHT JOIN task ON JobTask.TaskID = task.TaskID) ON positionTask.TaskID = task.TaskID) ON ActivityTask.TaskID = task.TaskID) INNER JOIN TaskCompetency ON task.TaskID = TaskCompetency.TaskID) ON Competency.CompetencyID = TaskCompetency.CompetencyID
WHERE (((JobTask.JobID)=[job])) OR (((positionTask.PositionID)=[position])) OR (((ActivityTask.GroupID)=[Activity]))
GROUP BY Competency.CompetencyKeyword;

It works fine, it returns the all the keyword, and its highest priority.
But I can’t return the Competency.CompetencyId, Competency. CompetencyName with the aggregate function Max

View 2 Replies View Related

Aggregate Query Problems

Jun 16, 2005

Hello! .... I think I posted this in the wrong section of the forum, so here it is again and hope someone's kind enough to help. Basically, I've been asked to show (in a form as well as a report) results on a Monthly, Quarterly and Year-to-Date basis. The table structure I currently have is as follows (additional fields and/or tables can be added if it would help get the results desired):

ID Date Volume/Value
A1 Jun-05 10,000
A2 May-05 15,000
A3 Apr-05 12,000
A4 ------ ------
An May-04 11,000
B1 Jun-05 10,000
B2 May-05 15,000
B3 Apr-05 12,000
B4 ------ ------
Bn May-04 11,000

Part of the trick with this is to be able to show these aggregate values (quarterly, YTD, and on a 13-month rolling basis) as users enter new data for whatever the current month is. Any assistance in helping me get this accomplished would be enourmeously appreciated.

View 1 Replies View Related

Problem Using With Max() Aggregate Function

Mar 10, 2007

I have a table in MS Access named Invoices containing the following values:

NameInvoice
Jim101
Jim102
Jane103
John104
Jane105
John106
John107
Jim108
Jim109
Jim110

The following query gives me the results you see below:

SELECT Name, Count(Invoice) AS CountOfInvoice
FROM Invoices
GROUP BY Name;

NameCountOfInvoice
Jane2
Jim5
John3

Ultimately, I want to design a SINGLE query that returns ONLY the Max(CountOfInvoice). In this case: 5.

Can anyone help me?

View 4 Replies View Related

Aggregate Function DSUM

Feb 17, 2008

Hi All,

I have a table namely, leave_details wherein there is a column called DAYS and TDAYS,
DAYS columns represent number of leaves an employee has taken in a single leave application and
TDAYS represents Total number of leaves an employee has availled till date.

To calculate the TDAYS i need to SUM the DAYS column for every employee.

When an employee again log in the application he should be aware of the total number of leaves which he has already availed.

Please help me for the same as I am unable to do so.

Kindly revert back for any queries or any further information.

With Warm Regards,
Sanjay

View 3 Replies View Related

Aggregate Function Error

Apr 3, 2008

Hi,

I am working on a marketing database. I have two tables that are in use called tblListInfo and tblResults.

In tblListInfo the fields I am using are DropDate, ListName and Marketcode.
In tblResults the fields I am using are JoinDate and Marketcode.

I am attempting to write a query that the end result will display the number of records within the 1st week of the promotions drop date. For every combination where the ListName value AND the DropDate value are unique, I want to count the number of records in tblResults that have JoinDate BETWEEN DropDate AND DropDate + 6.

I tried writing the code for this in pieces starting with:

SELECT DISTINCT tblListInfo.[ListName], tblListInfo.[DropDate]
FROM tblResults
INNER JOIN tblListInfo
ON tblResults.[Marketcode] = tblListInfo.[Marketcode]

This gave me my first ideal part of the output I was seeking. I listing of all the possible marketing lists used with duplicate names occuring only when there were different drop dates. for example:

Listname Drop Date
List - 1 1/1/08
List - 1 1/15/08
List - 2 1/1/08
List - 2 2/5/08

From there I wanted to modify the process to include a third field that included the # of records in tblResults that have the JoinDate field BETWEEN tblListInfo.[Drop Date] AND (tblListInfo.[Drop Date]+6). The ideal output would be like this:

Listname Drop Date Week 1
List - 1 1/1/08 15
List - 1 1/15/08 8
List - 2 1/1/08 32
List - 2 2/5/08 12

My attempt at getting to this result is below:

SELECT DISTINCT tblListInfo.[drop date], tblListInfo.[ListName], count(tblResults.[join date]) AS [Week 1]
FROM tblResults
INNER JOIN tblListInfo
ON tblResults.[MarketCode] = tblListInfo.[MarketCode]
WHERE tblResults.[Join Date] BETWEEN tblListInfo.[drop date] AND (tblListInfo.[drop date]+6)

I receive an error at this point saying: "You tried to execute a query that does not include the specified expression 'ListName' as part of an aggregate function."

Can anyone offer suggestions/corrections to my approach and logic?

View 2 Replies View Related

How To Sum Domain Aggregate Controls?

Oct 14, 2005

I have a subform (in continuous record view) which displays products on an order items form. Each record has a DMin control to find the lowest price for its product (each product can have many suppliers and therefore many prices). I need to put a control in the subform footer containing the sum of the minimum prices.

Access doesn't seem to want to sum calculated controls, so could someone please point me in the direction of how to go about doing this or achieving the same result through a different method?

View 10 Replies View Related

Query Help - Aggregate Function

Dec 15, 2004

Hi all,

I need some help in sorting out this query.

My Table looks like this..

Manifest Weight StopNo State
------- ------- ------- ------
71545 1000 10 VA
71545 1000 20 TN
71545 500 30 AL

Some Manifests are repeating
There are multiple stops within each manifest (eg.: 10, 20, 30) and each stops has itsown weight info and State.
What I am trying to figure out is the number of manifests, total weight within manifest, and the last stop and state of the manifest

Query:
SELECT ManifestNo, Count(ManifestNo), Sum(Weight), Last(Stop No), Last(State)
FROM RBP
WHERE ShipmentType="ROAD"
GROUP BY ManifestNo;

My Problem: Everything works fine, but the state of the last stop is not displaying properly..

My result is - 71545 3 2500 30 TN
Correct output should be - 71545 3 2500 30 AL

I tried to remove the "last" from State but its gives me the aggregate function error. Any suggession ?
Thanks in advance..
binjos

View 2 Replies View Related

Mail Merge With Aggregate Func

Mar 31, 2008

Morning!

I have quite a few aggregate functions within a report I produced based on 2 queries. I need to do a mail merge with this information, but it is not all in one single query, because I cannot get an expression using totals in a field. How do you get these aggregate functions to be a part of the query, so it can eventually be part of the mail merge? It is unfortunate that you can't just use a report to mail merge, because all my equations are in the report.

View 11 Replies View Related

Count Property In Aggregate Query

Jul 19, 2005

I have an aggregate query that creates a report of "A" by "B". Each "A" can have many "B"s under it. The report works fine. I need to count and display on the report the number of "B"s for each "A". I can't seem to manipulate the "count" property of the query to generate the correct number. Little help??? Thanks....

Mike T

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved