A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

AVG BETWEEN DAYS



 
 
Thread Tools Display Modes
  #1  
Old June 4th, 2010, 09:03 PM posted to microsoft.public.access.queries
Lucy
external usenet poster
 
Posts: 103
Default AVG BETWEEN DAYS

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.


  #2  
Old June 5th, 2010, 10:53 PM posted to microsoft.public.access.queries
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default AVG BETWEEN DAYS

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/For...eries/201006/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:03 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.