Unanswered: ms access 2010 query to compare records in the same table and calculate a moving aver
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/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
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
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.