Jump to content

Fuel Consumption


This topic is 2218 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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?

 

Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 2218 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.