Results 1 to 4 of 4
  1. #1
    neuk is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    36

    Offset Record by One


    I created a query that calls a public function “bmr (id as long) as double”. The function calculates recursive values by creating a dictionary for each record which I then associate by id. The values populate in the field called “current” as follows:

    id weight density current
    10 60 700 0.2982
    11 60 700 0.5964
    12 60 700 0.8946
    13 60 700 1.1928
    14 60 700 1.491

    The problem is that the current field only represents the value after the day is past. I also need to reflect the value for the previous day on the same record to complete some other calcs. So it should look something like this:

    id weight density previous current
    10 60 700 0 0.2982
    11 60 700 0.2982 0.5964
    12 60 700 0.5964 0.8946
    13 60 700 0.8946 1.1928
    14 60 700 1.1928 1.491

    Notice that the previous field is the exact same thing as the current field except that it is offset by 1. I’ve been playing around with sql all day and I can’t seem to offset the previous field by one like what I’ve shown above. Since it seems that Access doesn’t have a Lag() feature available I thought I’d create a sub query which I have also been unable to make work – bear in mind that I’m not too familiar with sql.

    My other thought is just to recalculate the previous field in vba something like this:

    Code:
    Do Until rs.EOF
            i = i + 1
            If i = 1 Then
               bmg = 0
            Else
                bmg = CLng(60) * 700 / 1000 * 0.71 * 0.01 + bmg
            End If
            dict.Add (rs!id), (bmg)
            rs.MoveNext
        Loop
    Basically, I use i to start the first calculation at 0 and then from that point forward I start the recursive calculations which would offset the values. The problem I encountered with this approach is that when I store bmg as 0 on the first iteration, the ending result is the dictionary uses the record id for whatever reason. I’m not sure why it’s doing that – can double store 0? Also, if this were to work, I’d basically have to call a separate function for the previous field and then another function for current field, and I don’t care for redundant coding that much.

    Not sure which approach you’d recommend for the sake of simplicity and efficiency. Any help would be appreciated. I created a small database to see exactly what I’m talking about. Thank you for your help.

    You can look at the database here:


  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Consider:

    SELECT myTable.id, myTable.weight, myTable.density, bmr([id]-1) AS previous, bmr([id]) AS [current] FROM myTable;

    This assumes ID is sequential with no gaps. If this is not the case, gets more complicated.

    I've never used dictionary, only array - learning something new. Need to modify code so that if ID is not in dictionary it returns 0 instead of the ID. Don't need to loop dictionary.

    If dict.Exists(id) Then bmr = dict(id)

    Certainly Double variable can store 0.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Try this:
    Code:
            SELECT TOP 1 myTable.id
                ,myTable.weight
                ,myTable.density
                ,0 AS previous
                ,bmr([id]) AS CURRENT
            FROM myTable
            WHERE id = (
                    SELECT min(id)
                    FROM mytable
                    )
    
            UNION
    
            SELECT previous.id+1
                ,previous.weight
                ,previous.density
                ,bmr(previous.id) AS previousVal
                ,bmr(mytable_1.id) AS currentVal
    
            FROM myTable AS previous
                ,myTable AS myTable_1
            WHERE previous.id = mytable_1.id - 1

    My test:

    id weight density previous CURRENT
    10 60 700 0 0.2982
    11 60 700 0.2982 0.5964
    12 60 700 0.5964 0.8946
    13 60 700 0.8946 1.1928
    14 60 700 1.1928 1.491

    Good luck with your project.

    Note: Updated--Original did not use proper ID in result.
    Last edited by orange; 05-21-2020 at 10:26 AM. Reason: Adjusted the ID to reflect proper values

  4. #4
    neuk is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    36
    Thank you for your help. You both provided ways to resolve my problem in ways that never occurred to me.


    Note: I have edited this post in error. I was working with my previous post where I changed the SQL to get the proper ID. And opened this post by mistake. Orange.
    Last edited by orange; 05-21-2020 at 10:26 AM. Reason: Adjusted the ID to reflect proper values

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

Similar Threads

  1. Replies: 4
    Last Post: 07-12-2018, 05:13 PM
  2. Replies: 5
    Last Post: 05-23-2016, 10:57 AM
  3. open to new record offset
    By ShostyFan in forum Access
    Replies: 2
    Last Post: 02-07-2015, 06:02 PM
  4. Replies: 1
    Last Post: 08-12-2013, 02:05 PM
  5. Runtime error with ActiveCell.offset
    By Eowyne in forum Programming
    Replies: 2
    Last Post: 04-22-2011, 10:31 AM

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