Formula for taking the difference between the last two entries?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

Is there a formula that will calculate vehicle mileage?  I have a parent table that has the vehicle info linked to a child table that has odometer reading.  Is there a way to get the miles driven for a particular month?  ie the difference between the last two entries?


Photo of Jared

Jared

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Nio problem,

So 1 vehicle has many mileage entries.  Now it is not clear from your post if the user has entered just 1 reading per month.  But I will answer the question as it was posted - difference between the last two entries.  I will also deal with the assumption that the entries are entered in sequence so the newest record ID is the most recent entry.  If that is not the case, then  there are ways to handle that too.

1. Make a summary of the Maximum of the Record ID of the Mileage Entries and call it [Record ID of the most recent Mileage Entry]

2. Look that up down to the child table of Mileage Entries.

3. Make a summary of the Maximum Record ID of the Maximum of the Record ID of the Mileage Entries but subject to the filter that the [Record ID#] is less than the [Record ID of the most recent Mileage Entry]. Call it [Record ID of the 2nd most recent Mileage Entry] 

4. Look that field up down to the Mileage Entries table.

5. Make a Summary Maximum of the Mileage subject to the filter that the [Record ID#]  is equal to the value in the field [Record ID of the most recent Mileage Entry].  There will only be one "hit".

6. Make a Summary Maximum of the Mileage subject to the filter that the [Record ID#]  is equal to the value in the field [Record ID of the most recent Mileage Entry].  There will only be one "hit".  Call it [Most recent Mileage]

7. Make a Summary Maximum of the Mileage subject to the filter that the [Record ID#]  is equal to the value in the field [Record ID of the 2nd most recent Mileage Entry].  There will only be one "hit".  Call it [2nd Most recent Mileage]

I would suggest also repeating steps 6 and 7 to get the dates as well for those two Mileages (ie Summary maximum of the [date].
You can now subtract those two Mileages on the Parent Vehicle record and call it most recent Month's Mileage and note the dates of the Mileage readings.