Counting data for the past 30 days

[Keith13]

I am trying to count the past 30 days order count but when doing so I get multiples of the same item for each day it was ordered. Is there away to count the past 30 days without list each individual day that it was purchased below is the sql and result example

SELECT Combine_Orders_ORder_Delivery_Group.ITEM_ID, Count([Combine_Orders_ORder_Delivery_Group]![ITEM_ID]) AS Item_Occurance, Combine_Orders_ORder_Delivery_Group.ORDER_DATE
FROM Combine_Orders_ORder_Delivery_Group
GROUP BY Combine_Orders_ORder_Delivery_Group.ITEM_ID, Combine_Orders_ORder_Delivery_Group.ORDER_DATE
HAVING (((Combine_Orders_ORder_Delivery_Group.ORDER_DATE) Between Date() And DateAdd("m",-1,Date())));

EX

item id	amount	date
123	4	11/10/2016
123	1	11/11/2016
123	7	11/12/2016
456	1	11/10/2016
456	4	11/11/2016
456	3	11/12/2016
789	9	11/10/2016
789	8	11/11/2016
789	4	11/12/2016

<tbody>
</tbody>

 

[Keith13]

Would making another query and summing up the amount column give me the correct result?

 

[chicagocomputerclasses]

I assume access should work similar to regular SQL commands, so I would try this

SELECT Count([Combine_Orders_ORder_Delivery_Group]![ITEM_ID]) AS Item_Occurance FROM Combine_Orders_ORder_Delivery_Group
HAVING (((Combine_Orders_ORder_Delivery_Group.ORDER_DATE) Between Date() And DateAdd("m",-1,Date())));

 

[Joe4]

Is your screen print what you are getting now, or your desired result?
If it is what you are getting now, what is it that you want to see?
Can you post an example?

 

[Keith13]

The table above is is what I get now the desired result would be one row for each item id and the amount totaled

 

[Joe4]

Remove the date field from the SELECT, GROUP BY and HAVING clauses, and add it just to the WHERE clause instead.

SELECT Combine_Orders_ORder_Delivery_Group.ITEM_ID, Count([Combine_Orders_ORder_Delivery_Group]![ITEM_ID]) AS Item_Occurance
FROM Combine_Orders_ORder_Delivery_Group
WHERE Combine_Orders_ORder_Delivery_Group.ORDER_DATE Between Date() And DateAdd("m",-1,Date())
GROUP BY Combine_Orders_ORder_Delivery_Group.ITEM_ID;