help to calculate mileages

[james7705]

hi all

could anyone help me with a macro or code. what i have is a table for all my vehicle details. i have another table which contains the mileages on the vehicles. what i need to do is when, lets say vehicle A refuels, i need to have the mileage entered into a table. when vehicle A gets refueled again, the mileage needs to be entered again and the previous and current mileage needs to be calculated. this needs to work for all vehicles from A to Z

[Bob Fitz]

I think you will need a table to record details of each refuel.
You would need the following fields:
VehicleID field which would have the value of the Primary Key from your vehicle table
RefuelDate, to record the date when the refuel took place.
CurrentMileage, to record the mileage reading when the refuel was made.

Not sure exactly what calculations you want to make but the results should not be saved in a table, better to calculate these whenever and wherever they are required.

[June7]

For calculating the miles between fillups, review http://allenbrowne.com/subquery-01.html#AnotherRecord

[rpeare]

assuming there is a date associated with each refueling (and time if it's refueled more than once a day) you could do something like this:

James7705.zip

if you fill up more than once a day you can use the timestamp as well, just bear in mind this is relying on a link that is not the PK so you have to allow for what constitutes a unique record in the absence of the PK

[james7705]

Hi Bob.
i already have a table containing VehicleID, RefuelDate, ClosingMileage, FuelQuantity, Driver.
What I need to do, is have a Report that will calculate the total mileage traveled from last refuel for that vehicle and also calculate the fuel consumption of the vehicle.
Is this possible?

[June7]

Did you download the db posted by rpeare?

[james7705]

Hi rpeare

Yes there will be a date involved, but the office staff will be entering the data received from the fuel slips my technicians bring in. The vehicles fill up about once a week, sometimes more.your sample looks like it could work. i will try it tomorrow and let you know.
My vehicle table has a lot more fields though... tblVehicles ID; Make; Model; Year; Colour; DatePurchased; Registration; VIN

[james7705]

Sorry PK? I dont understand that

[james7705]

Yes I did. Going to try it out at work tomorrow. It looks like it could be exactly what I need

[June7]

PK = Primary Key
FK = Foreign Key

[james7705]

ok

Thanks to all of you for your help and advice. I have tried the sample posted by rpeare and it will work perfectly for me.

thanks again