Problem with MYSQL QUery

[Chris C]

I'm trying to build a database to monitor vehicle maintenance. Vehicles have different inspections (minor every 7 days in traffic, intermediate after they have run 14 days etc.).

Becaus they don't run every day, the days they ran are logged in tblRunningDays (dayVehicle and dayDate)

tblMaintenance contains the date each exam was carried out .

tblExams just gives details of the type of exam

tblDesignations just gives information on the type of vehicle.

So far, I have the following query.

SELECT tblVehicles.vclID, tblDesignations.desLabel, tblVehicles.vclFleetNumber, tblMaintenance.mntDateCompleted, MAX(tblMaintenance.mntDateCompleted) as lastExam, tblExams.exmTitle, tblExams.exmFrequency, COUNT(tblRunningDays.dayDate) AS daysRun, (tblExams.exmFrequency - COUNT(tblRunningDays.dayDate)) AS daysRem
FROM tblVehicles
RIGHT JOIN tblMaintenance ON tblVehicles.vclID = tblMaintenance.mntVehicle
INNER JOIN tblDesignations ON tblVehicles.vclDesignation = tblDesignations.desID
INNER JOIN tblExams ON tblMaintenance.mntExam = tblExams.exmID
INNER JOIN tblRunningDays ON tblVehicles.vclID = tblRunningDays.dayVehicle
WHERE tblExams.exmTime = FALSE and tblMaintenance.mntDateCompleted > tblRunningDays.dayDate
GROUP BY tblVehicles.vclID, tblExams.exmID
ORDER BY daysRem ASC

This works correctly, but doesn't reset when a fresh exam is carried out. What I think I need is to change this bit:

WHERE tblExams.exmTime = FALSE and tblMaintenance.mntDateCompleted > tblRunningDays.dayDate

to refer to lastExam instead of mntDateCompleted, which you can't do.

Any ideas on how to fix this?