AVG BETWEEN DAYS
in [Queries]
|
From: Lucy on 4 Jun 2010 16:03 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.
From: KenSheridan via AccessMonster.com on 5 Jun 2010 17:53 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 Lucy wrote: >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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201006/1
|
Pages: 1 Prev: Looping through data with calculations Next: Querying for most recent value |