12 month rolling average by location

[ShanAnalytst]

Hello, I can successfully create a 12-month rolling average based upon data
in this format:
Year Month Value
2008 1 10
2008 2 20
2008 3 30
2008 4 35
2008 5 50
2008 6 51
2008 7 34
2008 8 37
2008 9 41
2008 10 52
2008 11 54
2008 12 60

(assume 2009 follows with different values and months 1-12). However, what
I really need to do is to create the rolling average based upon the year,
month AND location, such as this:

Year Month Location Value
2008 1 001 10
2008 2 001 20
2008 3 001 30
2008 4 001 35

(and so on...many locations would be involved reflecting the same year and
month but would have different values).

I have a subquery that looks like this:
SELECT RollingAvg1.Year, RollingAvg1.Month, RollingAvg1.Location,
RollingAvg1.Value, (SELECT AVG(Value)
FROM RollingAvg1 j
WHERE DateDiff("m",DateSerial(j.Year,j.month,1),
DateSerial(RollingAvg1.Year,RollingAvg1.Month,1))
Between 0 and 11) AS Rolling12Mo
FROM RollingAvg1;

I realize this needs to be modified to account for the locations but I
cannot figure out how to do it. I am not a programmer and would like to keep
it to something that I can understand, therefore would like to enhance the
existing subquery if possible.

Thanks in advance!

 

[John Spencer]

SELECT RollingAvg1.Year, RollingAvg1.Month, RollingAvg1.Location,
RollingAvg1.Value,
(SELECT AVG(Value)
FROM RollingAvg1 j
WHERE DateDiff("m",DateSerial(j.Year,j.month,1),
DateSerial(RollingAvg1.Year,RollingAvg1.Month,1))
Between 0 and 11
AND j.Location = RollingAvg1.Location) AS Rolling12Mo
FROM RollingAvg1;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County