Results 1 to 7 of 7
  1. #1
    Ohann is offline Novice
    Windows Vista Access 2010 64bit
    Join Date
    Jul 2011
    Posts
    4

    Access query to compare records in the same table and compute mov average

    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

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    What you're asking can be done, I'm enclosing an example.

    However, I would really strongly suggest that in the future you update these records as you are entering them (unless you're getting them from an outside source)

    The only thing missing is your average over x days. But you can take the updated table (after you run my module) and use the same type of formula I use for the 14 day average (davg over x many days instead of a static 14).

  3. #3
    Ohann is offline Novice
    Windows Vista Access 2010 64bit
    Join Date
    Jul 2011
    Posts
    4
    Hi RPEARE

    I am just working through your code now to check if I understand it at all but wanted to say thanks so long!! Also the answer is yes I do get the data from an outside source and it's not just one table but will be quite a few so do you think it would be ok to use the code you provided on all of them if I just change the references to the relevant database?

  4. #4
    Ohann is offline Novice
    Windows Vista Access 2010 64bit
    Join Date
    Jul 2011
    Posts
    4
    Hi again rpeare

    THANK YOU SO MUCH!! I certainly don't understand it all but from what I do understand it looks like it will do exactly what I wanted!!. Thanks for sharing this!

    Sorry for my dumb questions but I just need to make sure. Do I replace the items called "dailyid", "itemdate" and "itemvalue" with the actual field names in my database. Also how would I call this module to run as part of an existing access query so that the results are displayed in the query results?

  5. #5
    Ohann is offline Novice
    Windows Vista Access 2010 64bit
    Join Date
    Jul 2011
    Posts
    4
    Sorry me again. I am working through the code and changing the names of the columns and tables as per my database. I have included the adjusted vba below. When I get to the line just after rst.Movenext I get an error 3021 saying "No current record". In the select statement where you make the ID = 1 does this automatically select all records in the database or only the first? Am I doing anything wrong in the way I replaced the field names. My names are as follow: Database = SP500Data, ID = SP500ID, Date = SP500TradeDate, Value = SP500AdjClose.

    Thanks again for all your help!!

    Set db = CurrentDb
    sSQL = "SELECT * FROM SP500Data WHERE (([SP500ID]) = 1) ORDER BY SP500TradeDate"
    Set rst = db.OpenRecordset(sSQL)
    rst.MoveFirst
    'skipping doing anything to the first record of any given item
    'but loading the information for comparison to the next record
    dPrevItemID = rst.Fields("SP500ID")
    dPrevItemDate = rst.Fields("SP500TradeDate")
    dPrevItemValue = rst.Fields("SP500AdjClose")
    dBaseValue = dPrevItemValue
    rst.MoveNext
    If rst.Fields("SP500AdjClose") > dPrevItemValue Then 'set the direction to match the second record in the database
    sPrevdir = "+" 'using + for positive movement, - for negative and 0 for no movement
    ElseIf rst.Fields("SP500AdjClose") < dPrevItemValue Then
    sPrevdir = "-"
    Else
    sprevidr = "0"
    End If

    iDirCount = 0 'directioncount for adding a number to the up/down/nochange label

    Do While rst.EOF <> True
    dItemID = rst.Fields("SP500ID")
    dItemDate = rst.Fields("SP500TradeDate")
    dItemValue = rst.Fields("SP500AdjClose")
    rst.Edit
    rst.Fields("item14dayavg").Value = DAvg("[SP500AdjClose]", "tbl_Movement", "[SP500TradeDate] between #" & DateAdd("d", -14, dItemDate) & "# and #" & dItemDate & "#")
    rst.Update
    Debug.Print dItemDate & " " & dItemValue
    If dItemValue > dPrevItemValue Then
    sCurrDir = "+"
    iDirCount = PopulateDirCount(sCurrDir, sPrevdir, iDirCount) 'find the number of day in current direction
    rst.Edit 'edit the current record
    rst.Fields("itemdir").Value = "Up" 'set the item direction to 'up'
    rst.Fields("itemdircount").Value = iDirCount 'set the number of days in this direction
    rst.Update 'update the record
    ElseIf dItemValue < dPrevItemValue Then
    sCurrDir = "-"
    iDirCount = PopulateDirCount(sCurrDir, sPrevdir, iDirCount)
    rst.Edit
    rst.Fields("itemdir").Value = "Down" 'set the item direction to 'down'
    rst.Fields("itemdircount").Value = iDirCount
    rst.Update
    ElseIf dItemValue = dPrevItemValue Then
    sCurrDir = "0"
    iDirCount = PopulateDirCount(sCurrDir, sPrevdir, iDirCount)
    rst.Edit
    rst.Fields("itemdir").Value = "No Change" 'set the item direction to 'no change'
    rst.Fields("itemdircount").Value = iDirCount
    rst.Update
    End If


    If sCurrDir <> sPrevdir Then
    rst.MovePrevious
    rst.Edit
    rst.Fields("itemrunstart").Value = dBaseValue
    rst.Fields("itemrunchange").Value = dPrevItemValue - dBaseValue
    rst.Fields("itemrunpct").Value = (dPrevItemValue - dBaseValue) / dBaseValue
    rst.Update
    dBaseValue = rst.Fields("SP500AdjClose")
    rst.MoveNext
    Else
    rst.MovePrevious
    rst.Edit
    rst.Fields("itemrunstart").Value = Null
    rst.Fields("itemrunchange").Value = Null
    rst.Fields("itemrunpct").Value = Null
    rst.Update
    rst.MoveNext
    End If

    dPrevItemDate = dItemDate
    dPrevItemValue = dItemValue
    sPrevdir = sCurrDir
    rst.MoveNext
    Loop
    rst.MoveLast
    rst.Edit
    rst.Fields("itemrunstart").Value = dBaseValue
    rst.Fields("itemrunchange").Value = dPrevItemValue - dBaseValue
    rst.Fields("itemrunpct").Value = (dPrevItemValue - dBaseValue) / dBaseValue
    rst.Update

    rst.Close
    Set db = Nothing
    End Sub

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Quote Originally Posted by Ohann View Post
    Sorry for my dumb questions but I just need to make sure. Do I replace the items called "dailyid", "itemdate" and "itemvalue" with the actual field names in my database. Also how would I call this module to run as part of an existing access query so that the results are displayed in the query results?
    The dailyid is a primary key that I put on my table (I put primary keys on everything, it's a good practice even if you think you'll never need one you may so it's best to start doing it from the beginning) I don't actually use the dailyID for anything in the code (I thought I might but I didn't) so you can probably comment that out and not have any problems. The item date and item value would be (from your example) DATE and VALUE. I changed these field names to ITEMDATE and ITEMVALUE because both DATE and VALUE are reserved words in Access and should never be used for any fieldname by themselves. If your fields are named Date and Value I would suggest you change them to no reserved words as I have.

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Sorry me again. I am working through the code and changing the names of the columns and tables as per my database. I have included the adjusted vba below. When I get to the line just after rst.Movenext I get an error 3021 saying "No current record". In the select statement where you make the ID = 1 does this automatically select all records in the database or only the first? Am I doing anything wrong in the way I replaced the field names. My names are as follow: Database = SP500Data, ID = SP500ID, Date = SP500TradeDate, Value = SP500AdjClose.
    the no current record error means your SQL statement is finding no matching records.

    When I set the ITEMID = 1 that is an artificial unique identifier I put into my database with the assumption that there will be multiple different stocks (I'm guessing based on names these are stock prices)

    if there are multiple stocks in your database and you want to cycle through them all at the same time, you would want that initial SQL statement to cycle through the different fund types to populate the entire database so you'd have to add a loop outside the existing code to update that SQL statement for every new FundID

    it would be the same general procedure as cycling through the individual record you'd just have to use a GROUP BY statement so you're only picking out unique values.

    i.e.

    Set a recordset to read the unique ID's
    Set the SQL statement to use the current ID
    run the existing code
    move to the next ID\

    I don't know how your data is organized though so it's hard to say

    If you want to run one fund at a time your sql statment would have to include the actual value from the SP500ID field of your table.

    rst.Fields("item14dayavg").Value = DAvg("[SP500AdjClose]", "tbl_Movement", "[SP500TradeDate] between #" & DateAdd("d", -14, dItemDate) & "# and #" & dItemDate & "#")
    Yoou will have trouble with this item too because you're still referencing tbl_movement and not your table name (SP500Data) which I assume is a table and not a database name as you said in your post.

    Also, unless you update your table to include the same field names I have for calculation values you'll also have a problem, if you intend to use my setup make sure you include the columns:

    itemdir, itemcount, item14dayavg, itemrunstart, itemrunchange, itemrunavg (6 additional fields)

    If you only want to carry the itemrunavg that's fine you can comment out the other fields that are being updated and it should still run.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How To Compute Percentages
    By zephaneas in forum Queries
    Replies: 7
    Last Post: 06-20-2011, 12:40 PM
  2. Access compare and sum records in two tables
    By piszczel in forum Queries
    Replies: 8
    Last Post: 05-23-2011, 02:07 AM
  3. Compare records in 2 tables
    By RalphJ in forum Access
    Replies: 13
    Last Post: 03-06-2011, 07:43 AM
  4. Replies: 3
    Last Post: 05-19-2010, 10:08 PM
  5. Replies: 0
    Last Post: 08-01-2009, 12:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums