Need help with formula

[mark6455]

I need some help with a formula please.


I have a database that I store every detail of my vehicle expenses in. I wrote it, but it was years ago and I forgot a lot of the methods. What I need is the following.

I have a category called 'REGENS'. For those that do not know, I have a diesel engine which goes into a mode called regeneration every so many miles. I track the start and end of each regen.

I need to take the latest regen odometer miles that occurs and find the previous regen that occurred mileage and then subtract the difference to get a field that shows how many miles it was between each regeneration.

So, I need to use the previous regen END mileage, subtract it from the new regen START miles and get a number.

In my database, start mileage is called "START MILEAGE" and ending mileage is called "ENDING MILEAGE" on each record. I flag that the entry that indicates a regen by having a checkbox to indicate this entry is a regen cycle. I am trying to figure out when the checkbox (called LABEL84) is checked that I want the previous regen end mileage to subtract from the new entry's start mileage and show that number.

Any guidance appreciated. Thanks

[Welshgasman]

Your memory is worse than mine.
https://www.accessforums.net/showthr...384#post482384

And best not to name a checkbox label anything.

[mark6455]

I was so busy with work, I didn't have time back then. LOL. I am retired now so I have time. But now because of an issue with Nissan, I need the data. Too time consuming for manual calcs on 174 regens.

[June7]

In Access, this calls for a correlated subquery. Review http://allenbrowne.com/subquery-01.html#AnotherRecord

Other database platforms have special functions to accomplish this.

[DavidW2]

You’ll need to query entries where LABEL84 is checked (indicating a regen), then sort by date or mileage. For each new regen, subtract the previous record’s ENDING MILEAGE from the current START MILEAGE. In Excel, use =A2 - OFFSET(B2,-1,0) assuming A2 is START MILEAGE and B2 is ENDING MILEAGE. In a database, use a self-join or window function like LAG() to access the previous row.