Offset Record by One

1. Novice
Windows 10 Access 2016
Join Date
May 2019
Posts
25

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

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

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. Novice
Windows 10 Access 2016
Join Date
May 2019
Posts
25
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

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