Calculate difference between current and last occurrence

[ms1202]

I have a table containing details of each fleet related event. It contains the date, the vehicle registration, mileage, event type (taken from 'fuel', 'check', 'service', 'defect', 'carwash', 'goods'), gallons of fuel, cost, notes.

I want to add a field that calculates the miles per gallon since the last refuelling. To do this I need to be able to find the difference between the mileage of the current record and the mileage when the car was last fuelled and divide the answer by the number of gallons now put in. I'm a bit of a novice with Access and so don't know how to call the record with the highest date less than the current record's date in which fuel was the event type and the registration number was equal to the current record's registration number. Hope that makes sense! If it's the first record I want it to say "First Time".

Now the second bit; I also want to add a field calculating the average daily mileage since the last check. That again means finding the difference between this mileage and the last mileage where the type was check and the also the difference between the dates. Can anybody help?

 

[MyBoo]

Need to provide additional information:

You want to save this information in your table? Are you using forms to enter this data? You mention ONE table... are you storing all this data in one table?

Normally, you would let the form, query or report calculate the things you mention. You can store this data, but why? There are times when it's necessary, but it's not the norm.

 

[VicRauch]

ms1202,
Lets just do the "Find the record" first, then after we have the correct record "in-hand" we can work on the rest of the request.
Field names for the example will be: Set current record date to: CurrentDate. vehicle registration will be VehicleRegistration, mileage will be Mileage, event type will be EventType, date will be EventDate. The table name will be tblVehicleEvents.
Now, lets build the criteria that we can use to find the mileage from the last "fuel" event.
Criteria = "VehicleRegistration=sqdq & me!VehicleRegistration & dqsq AND EventDate=#dq & DMax("EventDate", "tblVehicleEvents", "VehicleRegistration=sqdq & Me!VehicleRegistration & dqsq AND EventDate<#dq & CurrentDate & dq#dq) & dq# AND EventType=sqFuelsqdq)
Because single quotes and double quotes can be VERY confusing when put together (ie: '" or "') I have used sq for single quote and dq for double quote in the statement for the Criteria. Every sq needs to be changed to a single quote ( ' ) and evey dq needs to be changed to a double quote ( " ).
If you use this Criteria just built in a DLookup statement, you will get the record you want. So, the DLookup will look like this:
LastMileage = DLookup("Mileage", "tblVehicleEvents", Criteria)
I assume you can handle the subtraction, etc once you get the LastMileage.
Be sure to change all the field names and the table name to the names you actually use before trying this example.
HTH, and after we get this working, then we can proceed to the rest of your request.
When this does not work the first time (they usually don't), be sure to tell us the error message, and include a copy of the code you are using!