Fuel Consumption

[Ninja]

I have created a table to record company vehicle fuel usage, which records the date, vehicle, mileage and fuel (litres) for each time a vehicle is refuelled.

I can calculate a vehicle's overall fuel consumption based on max(mileage) - min(mileage) / sum(fuel) for all records related to that vehicle.

But how can I calculate the fuel consumption for each individual record. I.e. current(mileage) - last(mileage) / fuel

I need some way to only compare the current record with the last fill-up for that vehicle.

Any ideas?

[comment]

The common method is to lookup the mileage from the most recent record that has the same vehicle ID.

P.S. Are you really using version 6?

[Ninja]

Ha, no version 12. Must be a profile default!

Well that's what I was thinking, but how do I ensure that I lookup the last record?

I can create a self-relate to ensure I only look at records for the same vehicle, but I can't think how to identify the last entry.

Could I set the relate to only those records with a mileage lower than the current record, and then look for the max value of those? That sounds like it would work right?

 

[Ninja]

...and it did!

 

Thanks for your help.

[comment]

11 hours ago, Ninja said:

Could I set the relate to only those records with a mileage lower than the current record, and then look for the max value of those?

I would prefer using an auto-entered serial number field for this, because (a) it cannot be affected by user entry error, and (b) mileage can roll over at some point. If you sort the related records by the same ID field (or by date), descending, you can look up the data directly from the first related record, without calculating Max().

Edited June 2, 2016 by comment

[Ninja]

The only problem with your solution (unless I'm mistaken) is that it requires each record to be entered chronologically so that the serial numbers are in the correct order, but as the records are entered administratively, there is sometimes a backload and they get entered in any order.

[comment]

6 hours ago, Ninja said:

but as the records are entered administratively, there is sometimes a backload and they get entered in any order.

If the records are not entered in chronological order, then you cannot use a lookup - no matter how your relationship is set up. Using an unstored calculation field will work, but you need to test how it affects the performance when you display a large number of records.