Re: aggregate function error

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

TommyT wrote:
You tried to execute a query that does not include the specified
function as part of an aggregate function

SELECT Sum([amt]) AS Expr1, Testsales.orderid, Testsales.product,
Testsales.amt
FROM Testsales
WHERE (((Testsales.product)="widget"));

Any one know why this generates an error?

In a Totals query all fields have to be either aggregated (sum, min, max, etc.),
or have Group By on them. You have three fields in that query with neither of
these.

It also makes no sense to include both amt and sum(amt) in the same query. The
aggregation (in this case sum) needs to be PER-SOME-FIELD or combination of
fields. Do you want the total amt for the order? If so...

SELECT Testsales.orderid, Sum([amt]) AS Expr1
FROM Testsales
WHERE Testsales.product = "widget"
GROUP BY Testsales.orderid

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com



.

Relevant Pages