MemphisVBA
Technical User
I have a report to make in Access that will indicate several metrics dealing with my companies fuel purchases. I need to calculate "cost per mile" and "mile per gallon" but need help with the query...
What I have right now for a data source is a table that has the vehicle VIN, transaction date, transaction time, odometer reading, gallons purchased, cost per gallon, and total cost of transaction, etc. We get this from our fuel card vendor.
I have a report that currently has the VIN with a group header, and the rest of the information (sorted by transaction date in ascending order) in the VIN group.
My problem is also compounded by the fact that not every odometer reading is recorded.
I was thinking I would do something like this:
(by VIN) - for each transaction for that vehicle - find the odometer reading for the first date and subtract that reading from the odometer reading for the next date, and so on -
I just don't have any idea how to do that?
If you have a better suggestion, please let me know.
What I have right now for a data source is a table that has the vehicle VIN, transaction date, transaction time, odometer reading, gallons purchased, cost per gallon, and total cost of transaction, etc. We get this from our fuel card vendor.
I have a report that currently has the VIN with a group header, and the rest of the information (sorted by transaction date in ascending order) in the VIN group.
My problem is also compounded by the fact that not every odometer reading is recorded.
I was thinking I would do something like this:
(by VIN) - for each transaction for that vehicle - find the odometer reading for the first date and subtract that reading from the odometer reading for the next date, and so on -
I just don't have any idea how to do that?
If you have a better suggestion, please let me know.
