Subquery count and sum

[thmsjlmnt3953]

Hi,

I am trying to run a query to count orders and sum qty from salesdetail and salesheader tables (count on header and sum of qty in detail) however it seems to be counting all the detail too?
It leads me to believe I need to subquery and join however not sure how?

SELECT public_salesheader.[invdate] AS [date], [cust] & "/" & [del] AS customer, Count(public_salesdetail.[qty]) AS orders, Sum(public_salesdetail.[qty]) AS items
FROM public_salesdetail INNER JOIN public_salesheader ON public_salesdetail.orderno = public_salesheader.orderno
WHERE (((public_salesheader.invdate)>=Date()-30))
GROUP BY public_salesheader.[invdate], [cust] & "/" & [del]
ORDER BY public_salesheader.[invdate], Count(public_salesdetail.[qty]) DESC;

 

[plog]

You are going to be more specific in what you expect the results to be. Perhaps demonstrate your issue with data. Provide 2 sets:

A. Starting sample data from your tables. Include table and field names and enough samples to cover all cases.

B. Expected results from A. Show what you expect the query to produce when you feed it the data from A.