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