Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011

    Unanswered: ms access 2010 query to compare records in the same table and calculate a moving aver

    Hi All

    I have just joined this forum and am hoping someone out there can help.

    Till about two weeks ago I did everything I had to do in excel but due to the size of the data have decided to transfer over to access. The initial steps went well until I had to start doing some calculation in a query to mirror the columns I used to have in excel. LOST I AM!!!

    I want to do 3 different things:

    Firstly I need to evaluate a value field and if this field is higher than the previous I want to write UP, if lower write Down and if the same write No change. I then want to evaluate this field and count the number of times it goes in the same direction before turning i.e Down1, Then Down 2 Then Down 3 until it goes up which should then be reflected by Up1.

    Secondly at the point where the last move in the same direction for that instance happens I want to calculate the total percentage move before the change. For example if it moved up five times in a row I want to calculate the total combined percentage move for the 5 up moves.

    Thirdly I would like to have two moving averages. The one should be for a fixed period being 14 days and the other one should be based upon a parameter that is passed when the query runs.

    I am sorry to ask all these questions at once but I had all this working in excel and it was so easy to do the calculation there. Now I have absolutely no idea how to do this. I have tried looking at the Dlookup formula and have tried to see if I can find some sort of loop code where the value of a parameter is increased for each time that the direction stays the same and reset to one when it changes but everything is so new to me that I actually have no idea what I am doing.

    Any help assistance would be greatly appreciated.

    Below is a table showing what I would like to have but the formatting changes once I paste it so I hope you understand.


    Date Value Direction Count Concatenated MA-parameter MA - 10 day
    11/1/2011 120
    11/2/2011 110 Down 1 Down1
    11/3/2011 150 Up 1 Up1 126.67
    11/4/2011 130 Down 1 Down1 130.00
    11/5/2011 140 Up 1 Up1 140.00
    11/6/2011 145 Up 2 Up2 138.33
    11/7/2011 146 Up 3 Up3 143.67
    11/8/2011 150 Up 4 Up4 147.00
    11/9/2011 148 Down 1 Down1 148.00
    11/10/2011 148 No change 1 No change1 148.67 138.7
    11/11/2011 147 Down 1 Down1 147.67 141.4
    11/12/2011 146 Down 2 Down2 147.00 145
    11/13/2011 143 Down 3 Down3 145.33 144.3

  2. #2
    Join Date
    May 2004
    New York State
    If I were you, I would have two separate tables; one with the data, one with the analyses. That's if you need a table with analyses. If you only need it for reporting purposes, then just run the VBA code at reporting time.

    That said, I would take the existing table and modify it. Give it a Primary Key, so it can be easily tracked. Your table would look like

    PK Date Value
    1 11/1/2011 120
    2 11/2/2011 110
    3 11/3/2011 150
    4 11/4/2011 130
    5 11/5/2011 140
    6 11/6/2011 145
    7 11/7/2011 146
    8 11/8/2011 150
    9 11/9/2011 148
    10 11/10/2011 148
    11 11/11/2011 147
    12 11/12/2011 146
    13 11/13/2011 143

    Now in VBA, open your table and loop through the necessary amount of records to obtain your data. If you really need to keep a running record of a daily analysis, which is unusual in Access, by all means go ahead and make another table containing these data. Your PK in the data table would be a Foreign Key in the analyses table.

    In any event, there's no reason why you can't populate a temporary table while building your report; especially for monthly or annual reports.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts