Moving Average with Multiple Criteria in MS Access

Posted on 2014-07-16
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 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.
    LVL 1

    Author Closing Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    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…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now