Moving Average in Access Query

G

Guest

Hi

I would like to calculate a 5-year running average of volumes in Access, but I am not sure how to do this. My query is set up where I have calculated volumes by zone and year. I noticed a function called Davg, but I have never used this and not sure how to apply it to my situation.

Below is an Excel example of what I would like to do

Zone Year Actual Movin
Vol Av
GOV_1A 2000 6,553 4,32
GOV_1A 2001 4,373 3,77
GOV_1A 2002 2,059 3,56
GOV_1A 2003 2,095 3,05
GOV_1A 2004 2,762 3,32
GOV_1A 2005 4,000 3,60
GOV_1A 2006 5,695 3,65
GOV_1A 2007 3,456 4,68
GOV_1A 2008 2,367 5,01
GOV_1A 2009 7,897 4,35
GOV_1A 2010 5,678 4,79
GOV_1A 2011 2,367 4,32
GOV_1A 2012 5,678 2,74
GOV_1B 2000 6,553 4,32
GOV_1B 2001 4,373 3,77
GOV_1B 2002 2,059 3,56
GOV_1B 2003 2,095 3,05
GOV_1B 2004 2,762 3,32
GOV_1B 2005 4,000 3,60
GOV_1B 2006 5,695 3,65
GOV_1B 2007 3,456 4,68
GOV_1B 2008 2,367 5,01
GOV_1B 2009 7,897 4,35
GOV_1B 2010 5,678 4,79
GOV_1B 2011 2,367 4,32
GOV_1B 2012 5,678 2,74

....et

I'll appreciate any help

Thank
Jad
 
J

John Smith

This air-code, so beware typos etc, but a query on the lines below should give
what you seek. Obviously you will need to substitute your own table name and
columns! I have assumed that year is a number, if it is actually a date you
will need DateAdd("yyyy", -5, year) instead of just subtracting five.

SELECT ac.zone, ac.year, ac.ActualVol, AVG(av.ActualVol) AS AvgVol
FROM TableName AS ac, TableName AS av
WHERE ac.zone = av.zone
AND ac.year = av.year
AND av.year BETWEEN ac.year - 5 AND ac.year
GROUP BY ac.zone, ac.year, ac.ActualVol

--
HTH
John

Jade said:
I would like to calculate a 5-year running average of volumes in Access, but
Click to expand...
I am not sure how to do this. My query is set up where I have calculated
volumes by zone and year. I noticed a function called Davg, but I have never
used this and not sure how to apply it to my situation.
 
Top