Self Join Query / Second highest value in query

[Jared]

I have a table containing service history for vehicles.

I'm trying to put together a report to display what services have been
completed on each vehicle for the current period & if the services were
carried out within a tolerance based on the vehicle mileage.

I've tried a few combinations of dlookup, attempted self join queries etc.
to determine the mileage of the service prior to the current one to work out
these intervals between services.

The second highest value in a query or dlookup/dmax seems to be beyond my
reach.
Could anyone point me in the best direction to overcome this?

Thankyou in advance.

 

[Ken Sheridan]

You can use a subquery to return the mileage at the last service. The
subquery needs to return the highest mileage where the vehicle is the same
and the service date is before the current one, so it would go something like
this:

SELECT VehicleID, Mileage AS CurrentMileage,
(SELECT MAX(Mileage)
FROM ServiceHistory AS SH2
WHERE SH2.VehicleID = SH1.VehicleID
AND SH2.ServiceDate < SH1.Servicedate)
AS PreviousMileage
FROM ServiceHistory AS SH1;

In a report based on this you can include an unbound text box with a
ControlSource of:

=[CurrentMileage]-[PreviousMileage]

to give the mileage between the services.

Ken Sheridan
Stafford, England