Rolling 12 Month Average

J

J. Johnstone

Could someone help me with a query to calculate the rolling average of
values? I have columns in my table of Year, Month and Value. I am trying
to calculate the 12 month rolling average of the Value. This is what the
ouput should look like with "12MoAvg" being the rolling average. How do I do
this in Access?


Year Month Value 12MoAvg
2003 1 10
2003 2 20
2003 3 30
2003 4 35
2003 5 50
2003 6 51
2003 7 34
2003 8 67
2003 9 44
2003 10 36
2003 11 65
2003 12 49 40.92
2004 1 52 44.42
2004 2 6 43.25
2004 3 2 40.92
2004 4 39 41.25
2004 5 41 40.50
2004 6 30 38.75
2004 7 46 39.75
2004 8 32 36.83
2004 9 61 38.25
2004 10 47 39.17
2004 11 37 36.83
2004 12 50 36.92

Thanks,
 
D

Duane Hookom

Try starting with something like:
SELECT tblJohnstone.*,
(SELECT AVG(Value)
FROM tblJohnstone j
WHERE DateDiff("m",DateSerial(j.Year,j.month,1),
DateSerial(tblJohnstone.Year,tblJohnstone.Month,1))
Between 0 and 11) AS MA12
FROM tblJohnstone;
 
J

J. Johnstone

THANK YOU!!! You solved the problem!!!

Duane Hookom said:
Try starting with something like:
SELECT tblJohnstone.*,
(SELECT AVG(Value)
FROM tblJohnstone j
WHERE DateDiff("m",DateSerial(j.Year,j.month,1),
DateSerial(tblJohnstone.Year,tblJohnstone.Month,1))
Between 0 and 11) AS MA12
FROM tblJohnstone;
 

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