Solved

# Moving Average with Multiple Criteria in MS Access

Posted on 2014-07-16
1,449 Views
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,
0
Question by:fb1990

LVL 47

Accepted Solution

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.
0

LVL 1

Author Closing Comment

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

## Featured Post

### Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…