Query for average units sold per month

[Sym]

I have a table that with the following fields

Item Number
Product Catagory
Quantity Sold
OrderDate

What i want to do is make a query that will sort out how many of each item is sold on average per month but i cant figure out how to make it. My initial thought is to make separate queries with each month as the criteria with a sum of the quantity sold then do another query to do the average but is there a better way, possibly with expressions for each month in a single query? any help would be much appreciated.

thanks

 

[isladogs]

Sounds a sensible approach to me
Monthly totals in first query then use that to get averages

 

[jdraw]

I think the 2 query approach is good.

first: QryItemsSoldByCategoryandMonth with sql

SELECT Count(tblSym.itemNumber) AS CountOfitemNumber
, tblSym.ProductCategory, Sum(tblSym.QuantitySold) AS SumOfQuantitySold
, Month([OrderDate]) AS OrderMonth
FROM tblSym
GROUP BY tblSym.ProductCategory, Month([OrderDate]);

Then second query QryFinalAvgItemsSoldByCategoryandMonth

SELECT QryItemsSoldByCategoryandMonth.ProductCategory
, Avg(QryItemsSoldByCategoryandMonth.SumOfQuantitySold) AS AvgOfSumOfQuantitySold
FROM QryItemsSoldByCategoryandMonth
GROUP BY QryItemsSoldByCategoryandMonth.ProductCategory;

Good luck


OOoops I see Colin has responded.

 

[isladogs]

Ah but you provided that little bit more....