Lookup specific data in a table

[mark6455]

I have a database for vehicle history and expenses. Within that database is a place for regeneration (of a diesel engine). The name of that source is "Regeneration". Since this function does not always occur, I need to extract certain data from time to time.

What I need to do is search the database for "Regeneration", note the "Date" for that, then subtract the most recent Regeneration "Start Mileage" from the prior Regeneration "Ending Mileage".

There are over 84 Regenerations that have occurred to date. So the end result should look like this:

Date: September 1, 2021
Start Mileage: 58,000
Ending Mileage: 58,020
Regen Length(in miles) 20

Any guidance appreciated!

[Micron]

I know what diesel regen is (was a class A truck/coach and automotive mechanic for about 25 years) so maybe I can help. You've shown the desired result but what's missing is what your data looks like. Also, I see nothing in the result that indicates which engine/vehicle this is for, so that's important too. If you really want to shorten this, post a db with some sample data. Alternatively, you could copy/paste & post (you should get a table here). A spreadsheet(s) as an attachment would be good, but a db would be best.

Worst case scenario, you might need a subquery.

[ArviLaanemets]

What you mean with <The name of that source is "Regeneration">? You have a table named Regeneration in your DB? What about other types of maintenance - do you have a separate table of every one of them (You did say it is a 'database for vehicle history and expenses'!)?

I'd have a table for vehicles - like:
tblVehicles: VehicleID, VehicleProducerID, VehicleModel, VehicleProduced, ...;
A table where you register vehicle owner(s) - like:
tblVehicleOwners: VehicleOwnerID, VehicleID, OwnerID, OwnedAtDate;
...
A table where you register all vehicle maintenance operations:
tblVehicleMaintenance: VehicleMaintenanceID, VehicleID, MaintenanceDate, MaintenanceType, MaintenanceCost, ...
A table where you register all possible maintenance types - like:
tblMaintenanceTypes: MaintenanceTypeID, MaintenanceTypeDescription (one of types will be with description like 'Engine Regeneration')

So when you need to get the info e.g. about last engine regeneration for certain vehicle, you have to query the table tblVehicleMaintenance for VehicleMaintenanceID with latest MaintenanceDate for given VehicleID and MaintenanceType (e.g. using a subquery which returns TOP 1 records for those VahicleID and MaintenanceType, ordered by MaintenanceDate descending), and then return the info from tblVehicleMaintenance with this VehicleMaintenanceID. Probably the best way to do this is to write an UDF (or several - an UDF for every value like date, cost, etc. to be returned) for this - with VehicleID and MaintenanceType as parameters.

[mark6455]

Thanks for replying. Due to my work schedule, it will take me a day or two to get a DB posted. Labor Day weekend is a big O.T. few days for me. On my way out now.

[mark6455]

I gave "regen" its own category. Other maintenance is (other than fuel) is listed elsewhere and the invoice for maintenance is scanned in to the database.