[Last Call] Learn how to a build a cloud-first strategyRegister Now


Moving Average with Multiple Criteria in MS Access

Posted on 2014-07-16
Medium Priority
Last Modified: 2014-07-16
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

Question by:fb1990
LVL 49

Accepted Solution

Dale Fye earned 2000 total points
ID: 40201048
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.

Author Closing Comment

ID: 40201078
Thank you so much.  It worked as expected.  Also, thank you for the advice on the reserved word.

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question