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