Moving Average with Multiple Criteria in MS Access

Hello Expert,

Can someone please help me with Moving Average with Multiple Criteria in MS Access.  I have ID field, month and amount.  I need access query to calculate 3 months moving average of the amount based on ID and MONTH as a fourth field.  The average needs to take into account the id and the date.  At every ID change i want the 3 months rolling average to start over.  Here is my example data:

ID      Month      Amount
581      2/1/2014      25868.09
581      3/1/2014      26802.28
581      4/1/2014      188104.21
581      5/1/2014      28287.91
331      2/1/2014      13329.73
331      3/1/2014      92683.86
331      4/1/2014      52038.82
331      5/1/2014      12445.06
365      2/1/2014      115772.8
365      3/1/2014      103212.42
365      4/1/2014      104874.87
365      5/1/2014      65564.38
738      2/1/2014      98232.24
738      3/1/2014      36660.88
738      4/1/2014      48719.59
738      5/1/2014      39901.64
901      2/1/2014      39464.73
901      3/1/2014      40187.85
901      4/1/2014      102446.54
901      5/1/2014      40110.11

Thanks,
LVL 1
fb1990Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
I think this should provide what you are looking for.  I've added a column to indicate how many months are in the moving average.

SELECT T1.ID, T1.[Month], T1.Amount
, Avg(T2.Amount) as MovAvg, Count(T2.Amount) as MonthsInAvg
FROM yourTable as T1
LEFT JOIN yourTable as T2
ON T1.ID = T2.ID
WHERE T2.[Month] <= T1.[Month]
AND T2.[Month] >= DateAdd("m", -2, T1.[Month])
GROUP BY T1.ID, T1.[Month], T1.Amount

BTW, Month is a reserved word, I would not use it as the name of a field in one of your tables.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fb1990Author Commented:
Thank you so much.  It worked as expected.  Also, thank you for the advice on the reserved word.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.