Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    mshannonjones69 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    15

    Return Calculation from Previous Records on Same Day/Date

    Hello! I'm a newbie, so bear with me please.

    My wife and I have an Access database with historical stock data and I need to create an expression for the
    Change Jump field that updates one of the main tables. Each day has many records from each stock symbol, so
    it is somewhat difficult because things aren't just 1 item per day, but many records in the same day/Date.

    I just need the expression to do this, but I don't know all the parts/syntax to get it done. Everything in
    brackets is a field:

    [Change Jump] = If any records with the same [Date] as this record and where the [Time] is before this record
    (military/Short format), then calculate the [High]-[Low] of that record and return that number. If not, then
    return a 0.

    This is what I have currently that does NOT work:
    IIf([Date]="& [Date] &" And [Time]<="& [Time] &" And [Change]>0.0169,[High]-[Low],"0")

    Thanks in advance!

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,232
    Some general comments:


    • time format is immaterial - date/time is stored as a number. You may be formatting to show only the date or time, but that may not be the underlying value - see more below
    • Both date and time are reserved words, using them for field names will cause problems at some point and generate misleading error messages which you will spend hours/days trying to resolve.


    To clarify a few things before a solution is provided

    • You do not want a change jump for the first record of the day (i.e. from the previous day)
    • where does the 0.0169 come in?
    • Are High and Low integer? double? currency? and if one of these why do you want a text value returned if change >0.0169? why not a zero? Concern is you can only return one datatype, your calc at the moment could return either a number or text depending.
    • Which record is change in? the 'current' record or the 'previous' record?
    • is change a calculated field?
    • are date and time separate fields?
    • can you confirm that date is always a whole number (no decimal element)
    • can you confirm that time is always less than 1



    date/time fields are stored as a special form of decimal number. The date is before the dp (today for example is 42584, tomorrow will be 42585) and time is expressed as the time now in number of seconds since midnight divided by the number of seconds in a day (86400) so now (for today) is 16:32 and the time is expressed as 42584.6885763889

    you can check the last two points for clarification by using the cdbl function on your two fields in a query. e.g. Expr1: cdbl([Date])

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,608
    I can't figure out what your IIf is trying to do. What are you trying to do with all those & characters? In any case that IIf is only going to refer to one record (the current one).

    Everything in brackets is a field:
    A field where? On a form or in a table?

    If any records with the same [Date] as this record and where the [Time] is before this record....
    So, if I understand you correctly, you are looking at a record for one stock symbol at a specific time, and you want to check for earlier existing records for that symbol on the same date? What happens if there is more than one - which earlier one do you use?

    Your requirement is certainly doable - it might require more than a single IIf statement, though - maybe a little VBA function. where do you want to do this?

  4. #4
    mshannonjones69 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    15
    John_G,

    This is "Wife" speaking. My husband says you may want to disregard his IIF statement and just pay attention to the long hand explanation.

    We want our [Change Jump] to calculate [High]-[Low] based on whether the [Change] field of the current record or the [Change] field of any previous record within the same [Date] is greater than 0.0169.

    The [Change] field makes a calculation with a numerical result that we have set to return up to 4 decimal places. We also have a field called [1 Cent Dip] that returns a 1 or a 0 based on the numerical result in the [Change] field.

    Before realizing our issue, the calculation was IIF([Change]>0.0169, [High]-[Low],0). However, this does not take into account if the [Change] was > 0.0169 in any previous record of the same date but at a time prior to the current record. We want the [High]-[Low] calculation to happen if the [Change] meets our criteria in the current record or any previous record of the same date.

    As my husband mentioned, we have a [Date] field and a [Time] field, so we were thinking at some point we could make an expression something like [Date] of previous record is equal to [Date] of current record and [Time] of previous record is equal to or less than [Time] of current record to consider the [Change] field of a previous record.

    Hopefully this makes some sense. Sounds like you have some ideas?

    Thanks

  5. #5
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,232
    still not quite clear what you are require


    table looks something like
    Date...........Time.....Change....High....Low
    30/6/2016...10:15...0.0112.....1........1
    30/6/2016...10:15...0.0212.....1........1
    30/6/2016...11:15...0.0111.....2........1
    30/6/2016...13:15...0.0125.....3........1


    We want our [Change Jump] to calculate [High]-[Low] based on whether the [Change] field of the current record or the [Change] field of any previous record within the same [Date] is greater than 0.0169.
    to me this means if the current record is 30/6 11:15 or 13:15, there is a previous record with a Change greater than 0.0169 so do the calculation and high/low would be calculated from the high/low in the current record

    your output would look like

    Date...........Time.....Change....High....Low...Ch angeJump
    30/6/2016...10:15...0.0112.....1........1.......null (first record of the day)
    30/6/2016...10:15...0.0212.....1........1.......0
    30/6/2016...11:15...0.0111.....2........1.......1
    30/6/2016...13:15...0.0125.....5........2.......3

    but you also say this

    we have a [Date] field and a [Time] field, so we were thinking at some point we could make an expression something like [Date] of previous record is equal to [Date] of current record and [Time] of previous record is equal to or less than [Time] of current record to consider the [Change] field of a previous record.
    So for the 13:15 which is the previous record to consider - 10:15 where change is greater than 0.0169 or 11:15 which is the previous record?

    Perhaps provide some example data like the above to illustrate exactly what you require.

    Also, you say this is to do with historical stock data - does this mean there should also be a stock ID field to be taken into account or do you only have one stock or are you happy the 'previous' record could be for another stock

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,344
    If it is Historical data you are checking, won't the time on all records for a specific stock for the same date be less then the current record time you are dealing with?

    Also where are you doing this calculation, when the current/new record is entered or some other action? Or you just looking to run a query to give you this information from the Historical data?

  7. #7
    mshannonjones69 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    15
    Bulzie...

    No, there are times AFTER the current record time in the data.

    It is running in an update query, not a form or anything like that. The data is imported daily around 5pm. We just look at the historical data each night, so all the data (record, previous records, records after the current record chronologically) has already happened in the past.

    The calculation for the Jumps (Change Jump) is not seeing prior Dips (Change).

    We calculate the dip in the “Change” field: [Daily Open] – [Low] (but only of the 1 minute time increment/current record)

    The Change Jump field is calculating the jump based on whether or not the “Change” is > 0.0169, but it is only looking at the “Change” of the current time increment/record. (I know I'm being kind of redundant, sorry.)

    We want Change Jump to look at the current Change, but also to look for any previous Change of previous records (1 minute data/imcrement) during the same day/Date of > 0.0169. However, currently this ONLY looks at the current record's Change.

    This is our current Change Jump:

    IIf([Change]>0.0169,[High]-[Low],"0")

  8. #8
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,608
    Given that explanation of what you need, your approach is very unlikely to work. You are making the assumption that the update query processes each record in sequence, one at a time, and that when any one record is being processed, updates to "previous" (whatever that means in an update query) records have been made and are known to the query.

    You cannot make that assumption - an Update query in this context will be totally unreliable.

    I think you are going need a VBA routine to accomplish what you need to do.

  9. #9
    mshannonjones69 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    15
    John_G,

    I wouldn't know where to start. :-(

    How could/should I export the VBA code out of Access to display it here for you?

  10. #10
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,232
    @mshannonjones69

    did you see post#5 to try to bring some clarity to the situation?

  11. #11
    mshannonjones69 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    15
    Ajax,

    Yes, I saw #5, but I will have to let my wife (much more knowledgeable in Access) respond this evening. I understand most of your post, but she will articulate a response better. ;-)

    thanks!!

  12. #12
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,344
    Yep I think you will need use vba to loop through the records. Not all syntax is right but maybe something like this would work:

    rs1 = Select * from Table ORDER BY StockID, Date, Time Asc

    vChangeFlag = 0
    vStockID = Null
    vDate = Null

    rs1.MoveFirst
    Do Until rs1.EOF
    If rs1!StockID = vStockID and rs1!Date = vDate then
    If rs1!Change > 0.0169 OR vChangeFlag = 1 then
    rs1.Update
    rs1!ChangJump = rs1!HIgh - rs1!Low
    rs1.Update
    vChangeFlag = 1 (setting this means that all records after this one for same Stock and Date will need to do the calculation)
    End If
    Else
    vChangeFlag = 0 (Set Flag to 0 on new Stock and Date)
    If rs1!Change > 0.0169 then (This part processes the first record in the table or record with new Stock and Date)
    rs1.Update
    rs1!ChangJump = rs1!HIgh - rs1!Low
    rs1.Update
    vChangeFlag = 1 (setting this means that all records after this one for same Stock and Date will need to do the calculation)
    End If

    vStockID = rs1!StockID
    vDate = rs1!Date
    End IF
    rs1.MoveNext
    Loop

  13. #13
    mshannonjones69 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    15
    Bulzie,

    Thanks for the code. Sorry, but I'm not quite clear on where to put this - in the VBA of my update query or as something that runs on our table once we update with new data?

  14. #14
    mshannonjones69 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    15
    Ajax,

    The data below illustrates our issue (hopefully):

    Symbol Date Time Open High Low Close Volume Date Time Daily Open Change Jump From Open Jump Change Jump
    RNVA Thursday, July 28, 2016 11:10 0.235 0.235 0.233 0.235 16,196.00 7/28/2016 11:10 $0.25 0.0172 -0.0149 0 0.0023
    RNVA Thursday, July 28, 2016 11:11 0.237 0.238 0.237 0.238 10,500.00 7/28/2016 11:11 $0.25 0.0127 -0.0124 0 0
    RNVA Thursday, July 28, 2016 11:12 0.238 0.25 0.238 0.25 800.00 7/28/2016 11:12 $0.25 0.0124 -0 0 0
    RNVA Thursday, July 28, 2016 11:13 0.247 0.25 0.247 0.247 19,199.00 7/28/2016 11:13 $0.25 0.0032 -0.0002 0 0
    RNVA Thursday, July 28, 2016 11:14 0.247 0.247 0.235 0.235 4,600.00 7/28/2016 11:14 $0.25 0.0149 -0.0032 0 0
    RNVA Thursday, July 28, 2016 11:15 0.247 0.247 0.247 0.247 700.00 7/28/2016 11:15 $0.25 0.0032 -0.0032 0 0
    RNVA Thursday, July 28, 2016 11:29 0.242 0.242 0.24 0.242 4,930.00 7/28/2016 11:29 $0.25 0.01 -0.008 0 0
    RNVA Thursday, July 28, 2016 11:32 0.244 0.244 0.239 0.244 1,680.00 7/28/2016 11:32 $0.25 0.0109 -0.006 0 0
    RNVA Thursday, July 28, 2016 11:33 0.239 0.239 0.239 0.239 1,432.00 7/28/2016 11:33 $0.25 0.0106 -0.0106 0 0


    At 11:10, the CHANGE met our criteria of being greater than 0.0169 and the CHANGE JUMP calculated [High]-[Low]. We want the Change Jump calculation to continue to occur until the end of the day after 11:10.

    This is all stock specific.

    Thanks!

  15. #15
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,344
    I guess I was thinking once you have the new records entered. Run this from a button on a form to calculate the ChangeJump values on all the records. Then you can create a query to look at the data as you need. Of course make a copy of your DB and data before doing this as you will need to test it to make sure it works.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 12-01-2014, 02:53 AM
  2. Replies: 1
    Last Post: 09-06-2014, 01:08 PM
  3. Replies: 9
    Last Post: 08-19-2014, 12:41 PM
  4. Replies: 8
    Last Post: 09-27-2012, 11:25 AM
  5. Replies: 4
    Last Post: 11-10-2011, 03:16 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 - Senior Forums