I seem to break my head over the following problem and can't get it sorted so am turning to this awesome community to see if someone knows the answer
I have a fleet database where I need to calculate MPG. I have looked on google but haven't found the answer (or not easily understandable for me) yet.
To make things worse, half the vehicles in the fleet are reading kilometers and the other half are reading miles. I can convert the kilometers to miles by a query but that is pulling the fleet apart into 2 tables/queries before any mpg calculation is done. And then of course the mpg itself. I understand that it needs to calculate the difference between current and last mileage, but have no idea how, and of course we are not talking 1 vehicle in a table but multiple.
Example table (where odo reading is either miles or km depending on which vehicle)
[tbl_fuelreceipts]
vehicle date odoreading liters price
1 02/02 32000 83 94
4 04/02 12480 112 113
2 04/02 25644 142 153
1 05/02 32845 92 103
4 06/02 13110 97 108
2 05/02 26122 102 113
Data of course is just a made up sample but gives an idea of the structure.
Any help in getting the calculation done (query built) is appreciated
Thanks!
I have a fleet database where I need to calculate MPG. I have looked on google but haven't found the answer (or not easily understandable for me) yet.
To make things worse, half the vehicles in the fleet are reading kilometers and the other half are reading miles. I can convert the kilometers to miles by a query but that is pulling the fleet apart into 2 tables/queries before any mpg calculation is done. And then of course the mpg itself. I understand that it needs to calculate the difference between current and last mileage, but have no idea how, and of course we are not talking 1 vehicle in a table but multiple.
Example table (where odo reading is either miles or km depending on which vehicle)
[tbl_fuelreceipts]
vehicle date odoreading liters price
1 02/02 32000 83 94
4 04/02 12480 112 113
2 04/02 25644 142 153
1 05/02 32845 92 103
4 06/02 13110 97 108
2 05/02 26122 102 113
Data of course is just a made up sample but gives an idea of the structure.
Any help in getting the calculation done (query built) is appreciated
Thanks!