Average shipments in any given month

[tsison7]

I have a ship history covering several years and I want to give the average
shipment in each of the 12th months (ie. (Shipments in January/(total
years)). So far I put this in the report to count Shipments in January.

textbox
= sum(iif(month([shipdate])=1,[qty],0))

Now, how do I count the number of January's where there are shipments? How
do I count the number of years within that date range of the shipments?

Thanks,

 

[KARL DEWEY]

Try this substituting your table and field names --
SELECT Format([shipdate],"mmmm") AS [Month], Sum([1st_Table].qty) AS
SumOfqty, Count([1st_Table].qty) AS CountOfqty, Avg([1st_Table].qty) AS
AvgOfqty
FROM 1st_Table
GROUP BY Format([shipdate],"mmmm"), Format([shipdate],"mm")
ORDER BY Format([shipdate],"mm");

 

[KARL DEWEY]

SELECT Format([holiday dates],"mmmm yyyy") AS [Month], Sum([1st_Table].XX) AS
SumOfXX, Count([1st_Table].XX) AS CountOfXX, Avg([1st_Table].XX) AS AvgOfXX
FROM 1st_Table
GROUP BY Format([holiday dates],"mmmm yyyy"), Format([holiday dates],"yyyymm")
ORDER BY Format([holiday dates],"yyyymm");