aggregate function error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TommyT - 14 Jan 2007 02:03 GMT

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?

Reply to this Message

Rick Brandt - 14 Jan 2007 02:11 GMT

> You tried to execute a query that does not include the specified
> function as part of an aggregate function
[quoted text clipped - 5 lines]
>
> 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

Signature

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

Reply to this Message

TommyT - 15 Jan 2007 04:24 GMT

Thanks,

I think that will work!

> > You tried to execute a query that does not include the specified
> > function as part of an aggregate function
[quoted text clipped - 23 lines]
> Email (as appropriate) to...
> RBrandt at Hunter dot com

Reply to this Message