Rolling 12 Month Average

[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,

 

[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. Johnstone]

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

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;