AVG BETWEEN DAYS

L

Lucy

Hello,
Within my qry I would like to calculate the average daily production for the
month.
The data Im working with is: production volume, dates (which is a daily
info.) and ID number. I'm working with tons of data that have production for
each day of the year dating back 5 or more yrs. However I'd like to narrow
my daily data to only 6months prior summed up to monthly data and averaged.
Please help!
Thanks for your help.
 
K

KenSheridan via AccessMonster.com

Assuming that you have one row per date you should be able to do this with a
query which groups by year and month of the each date and averages the
production volume, e.g.

SELECT YEAR([ProductionDate]) AS [ProductionYear],
MONTH([ProductionDate]) AS [ProductionMonth],
SUM([ProductionVolume]) AS [TotalMonthlyProduction],
AVG([ProductionVolume]) AS [AverageDailyProduction]
FROM [YourTable]
WHERE [ProductionDate] >=
DATESERIAL(YEAR(DATE()),MONTH(DATE())-6,1)
GROUP BY YEAR([ProductionDate]), MONTH([ProductionDate]);

This will restrict the results to the 6 months prior to the current month,
along with the current month. If you have different production volume values
for different categories of production per day you can also group by category
if you wish to give you the total and average per month per category.

Ken Sheridan
Stafford, England
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top