aggregate functions

  • Thread starter Thread starter Tegan
  • Start date Start date
T

Tegan

When this error comes up i dont know what to do to fix it. i have an
assignement that i need to have finished but i have no idea have to get past
this error. If someone could help me that would be great.
Thanks Tegan
You tried to execute a query that does not include the specified expression
<surname> as part of an aggregate function. (Error 3122)
You tried to execute a query that does not include the specified expression
as part of an aggregate function or grouping.

Possible cause:

a.. You did not enter an aggregate function in the TRANSFORM statement.
 
You used an aggregate function like Sum in a Select query:

SELECT Table1.Whatever, Sum([Test]) AS Expr1
FROM Table1;

That query should look like this:

SELECT Table1.Whatever, Sum(Table1.Test) AS SumOfTest
FROM Table1
GROUP BY Table1.Whatever;

This also works, where there is only 1 field being totaled:

SELECT Sum([Test]) AS Expr1
FROM Table1;
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
I tried what you said but i still got the same error message, i am not sure
if i put it in right but this is what my SQL looks like:
SELECT person.surname, person.first_name, SUM
(admission.discharge_date -admission.admission_date) AS Length_of_stay
FROM person, admission
WHERE person.person_id = admission.patient_id
AND surname LIKE 'Green'
AND first_name LIKE 'Graham';
and the same error message as before is still coming up
Arvin Meyer said:
You used an aggregate function like Sum in a Select query:

SELECT Table1.Whatever, Sum([Test]) AS Expr1
FROM Table1;

That query should look like this:

SELECT Table1.Whatever, Sum(Table1.Test) AS SumOfTest
FROM Table1
GROUP BY Table1.Whatever;

This also works, where there is only 1 field being totaled:

SELECT Sum([Test]) AS Expr1
FROM Table1;
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access


Tegan said:
When this error comes up i dont know what to do to fix it. i have an
assignement that i need to have finished but i have no idea have to get past
this error. If someone could help me that would be great.
Thanks Tegan
You tried to execute a query that does not include the specified expression
<surname> as part of an aggregate function. (Error 3122)
You tried to execute a query that does not include the specified expression
as part of an aggregate function or grouping.

Possible cause:

a.. You did not enter an aggregate function in the TRANSFORM statement.
Click to expand...
Click to expand...
 
You need to add a GROUP BY clause to your query.

SELECT person.surname, person.first_name, SUM
(admission.discharge_date -admission.admission_date) AS Length_of_stay
FROM person, admission
WHERE person.person_id = admission.patient_id
AND surname LIKE 'Green'
AND first_name LIKE 'Graham'
GROUP BY person.surname, person.first_name;


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Tegan said:
I tried what you said but i still got the same error message, i am not sure
if i put it in right but this is what my SQL looks like:
SELECT person.surname, person.first_name, SUM
(admission.discharge_date -admission.admission_date) AS Length_of_stay
FROM person, admission
WHERE person.person_id = admission.patient_id
AND surname LIKE 'Green'
AND first_name LIKE 'Graham';
and the same error message as before is still coming up
Arvin Meyer said:
You used an aggregate function like Sum in a Select query:

SELECT Table1.Whatever, Sum([Test]) AS Expr1
FROM Table1;

That query should look like this:

SELECT Table1.Whatever, Sum(Table1.Test) AS SumOfTest
FROM Table1
GROUP BY Table1.Whatever;

This also works, where there is only 1 field being totaled:

SELECT Sum([Test]) AS Expr1
FROM Table1;
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access


Tegan said:
When this error comes up i dont know what to do to fix it. i have an
assignement that i need to have finished but i have no idea have to get past
this error. If someone could help me that would be great.
Thanks Tegan
You tried to execute a query that does not include the specified expression
<surname> as part of an aggregate function. (Error 3122)
You tried to execute a query that does not include the specified expression
as part of an aggregate function or grouping.

Possible cause:

a.. You did not enter an aggregate function in the TRANSFORM
statement.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top