Last entry for Maintenance Scheduler

[Guest]

I'm creating a maintenance scheduler for a fleet of trucks. I'd like to
create a form that shows the current status of the 3 maintenance items that
are to be performed for each truck. As maintenance is performed entries are
made to a Maintenance table where the unit, and Maintenance item are chosen,
and the current Mileage and Date are entered. I'd like to display date and
mileage of the last entry (greatest Mileage or date) for each Maintenance
item and show how many miles until the next scheduled maintenance is due.
How am I to do this??

Any help would be greatly appreciated,
Thanks in Advance

 

[Guest]

You will need another table listing the EqNum, MaintCode, Maint Work,
Interval-1, and Interval-2.
Like ---
Ford-1 1 Oil-filter 3000 90

Your MaintItems table will need EqNum, DateDone, MaintCode, Mileage.
Ford-1 2/12/2005 1 33000

SELECT MaintInterval.EqNum, MaintItems.MaintCode, Max(MaintItems.Mileage) AS
[Performed at miles], Max(MaintItems.DoneDate) AS [Date Comp],
MaintInterval.[Interval-1], MaintInterval.[Interval-2]
FROM MaintInterval INNER JOIN MaintItems ON MaintInterval.EqNum =
MaintItems.EqNum
GROUP BY MaintInterval.EqNum, MaintItems.MaintCode,
MaintInterval.[Interval-1], MaintInterval.[Interval-2]
HAVING (((MaintInterval.EqNum)=[Enter EqNum]) AND
((MaintItems.MaintCode)=[Enter MaintCode]) AND
((Max(MaintItems.DoneDate))<Date()-[Interval-2])) OR
(((MaintInterval.EqNum)=[Enter EqNum]) AND ((MaintItems.MaintCode)=[Enter
MaintCode]) AND ((Max(MaintItems.Mileage))<[Enter Mileage]-[Interval-1]));