Sum query does not work

[BasicBoy]

I have this query :

S$ = "Select sum(maintrans.quantity*maintrans.unitpriceexcl) as CatTotal, items.category from maintrans left join items on maintrans.itemno=items.orderitemno where items.category=" & rsCat!catno

The error says : You tried to exectue a query that does not include 'category' as part of an aggregate function.

Can somebody please help ?
Thanks

 

[SkipVought]

Hi,

S$ = "Select sum(maintrans.quantity*maintrans.unitpriceexcl) as CatTotal, items.category from maintrans left join items on maintrans.itemno=items.orderitemno where items.category=" & rsCat!catno [b]Group By items.category[/b]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

As you already know the category:

S$ = "SELECT Sum(M.quantity*M.unitpriceexcl) AS CatTotal FROM maintrans M LEFT JOIN items I ON M.itemno=I.orderitemno WHERE I.category=" & rsCat!catno

Furthermore, the WHERE clause defeat the outer join ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886