Running 3 Month Average

[SPC1715]

Hi all,

I'm relatively new to Access and had a question that doesn't seem so simple to me.

I need to create a query that will take the rolling 3 month average of my value fields. The date option will remain dynamic for the user so date ranges could range between 3 months to 2 years. Here is an example of the table the query pulls off:

Table Sample
Name YearMonth Total
A 2008-01 5
A 2008-02 6
A 2008-03 19
A 2008-04 2
A 2008-05 29

The query will need an output such as this

Name YearMonth Total Avg
A 2008-01 5
A 2008-02 6
A 2008-03 19 10
A 2008-04 2 9
A 2008-05 29 16.66

It will need to take the first three months and average them (10). Then it will take the 2nd, 3rd, and 4th record and average them for the next field (9)...and so forth.

I was able to successfully create a running average but not sure how to incorporate the 3 month stickler. Preferably could this be done in the design view or does it have to be done in the SQL view (i'm a noob).

Any help is much appreciated.

Thanks!

 

[khawar]

Download attached sample and check query "MovingAverage"

 

[SPC1715]

Wow! Thanks Khawar!!!

I actually found a method using a ranking but your version is much cleaner. Thanks again!