[SOLVED] MS access query aggregation

MS access query aggregation

I am trying to get query like this

SELECT sales.action_date, sales.item_id, items.item_name, 
       sales.item_quantity, sales.item_price, sales.net 
FROM sales INNER JOIN items ON sales.item_id = items.ID 
GROUP BY sales.item_id 
HAVING (((sales.action_date)=[Forms]![rep_frm]![Text13].[value]));

Every time I try to show data this message show

your query does not include the specified expression ' action date ' as part of aggregate function.

and for all field in the query >>> but i just want the aggregation be for item_id

what i should do?

Solution

You don't have any aggregations like SUM in your SELECT statement. I also don't understand why you sales.action_date is in de HAVING clause. This is for aggregated filtering like SUM(sales.item_price) <> 0. It should be possible to put this part in de WHERE-clause, before the GROUP BY instead of the HAVING clause. This example should work:

SELECT sales.item_id, items.item_name, SUM(sales.item_quantity), 
       SUM(sales.item_price), SUM(sales.net) 
FROM sales INNER JOIN items ON sales.item_id = items.ID 
WHERE sales.action_date=[Forms]![rep_frm]![Text13].[value] 
GROUP BY sales.item_id, items.item_name;