SQL error 3122

[dogdays]

Win 732, Access 2003
I have an SQL statement that when I try to execute it returns error 3122. You tried to execute a query that does not include the specified expression 'nHouseholdNumber' as part of an aggregate function. The statement is :

SELECT nHouseholdNumber, nIndividualNumber, dtDateEntered,  tSolicitor, tAcknowledgedBy, SUM(cAmount) as 'cTotalAmount'  FROM tblFiscalGifts WHERE nIdentity > 0  AND (dtDateEntered >= #8/3/2011# AND dtDateEntered <= #10/03/2011#) GROUP BY nIndividualNumber

Any advice is appreciated.

Jack

 

[psycoperl]

I run across this error many times when you are doing a group by and you leave out a field.

Fix would be:

SELECT nHouseholdNumber, nIndividualNumber, dtDateEntered,  tSolicitor, tAcknowledgedBy, SUM(cAmount) as 'cTotalAmount'  
FROM tblFiscalGifts
 WHERE nIdentity > 0  AND (dtDateEntered >= #8/3/2011# AND dtDateEntered <= #10/03/2011#) 
GROUP BY nHouseholdNumber, nIndividualNumber

 

[dogdays]

Thx psycoperl. Does this mean that the GROUP BY fields have to be the first column names after the SELECT statement, in order?

Jack

 

[Fazza]

hi, Jack

I understand the order of the fields doesn't matter. Basically, for every selected field without a SUM or MAX or MIN or COUNT or whatever repeats in the GROUP BY clause.

regards

SELECT some_field, another_field, and_one_more, SUM(whatever)
FROM table
WHERE some_field = 'optional_filter_value'
GROUP BY some_field, another_field, and_one_more
HAVING SUM(whatever)>10000

I've added a WHERE clause to show how to filter the dataset before the aggregate function (SUM in the example) and also a HAVING clause which goes after the GROUP BY and applies to the aggregated result.

 

[dogdays]

Fazza:
Thanks for the information, especially the HAVING clause.

Jack