I am trying to build a system to keep track of (rail) vehicle maintenance. I have several tables: tblVehicles - gives details of the individual designation tblDesignations - gives more detailed information about the vehicle type (not relevant to this) tblRunningDays - lists the days that each vehicle ran tblMaintenance - gives details of each inspection due, and when they are carried out. tblExams - gives details of the type of inspection - some are done based on number of days run, some are based on time irrespective of how many days the vehicle has run. I want to give two lists - one to show how many running days the vehicle has left until its next exam, and what type of exam (from tblExams) it is due - this could be a 7 day light exam, a 14 day moderate one etc. The other is to show which time-dependent exam is due - again, this could be a light 6-month exam, or a more in-depth 12 month exam. For the first one, I have the following query: SELECT tblVehicles.vclID, tblDesignations.desLabel, tblVehicles.vclFleetNumber, tblVehicles.vclBRNumber, tblMaintenance.mntDateCompleted, 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.exmTitle ORDER BY daysRem ASC;' This one kind of works, but seems to use the days run since the FIRST exam of each type, not the most recent one. Any ideas? I'm still meddling but rapidly running out of ideas. For the second one, I have the following query: SELECT tblVehicles.vclID, tblDesignations.desLabel, tblVehicles.vclFleetNumber, tblVehicles.vclBRNumber, tblExams.exmID, tblExams.exmTitle, tblMaintenance.mntID, MIN(DATE_FORMAT(tblMaintenance.mntDue, "%a %D %b %Y")) AS nextExam FROM tblVehicles RIGHT JOIN tblMaintenance ON tblVehicles.vclID = tblMaintenance.mntVehicle INNER JOIN tblExams ON tblMaintenance.mntExam = tblExams.exmID INNER JOIN tblDesignations ON tblVehicles.vclDesignation = tblDesignations.desID WHERE tblMaintenance.mntDateCompleted IS NULL AND tblMaintenance.mntDue > CURRENT_DATE GROUP BY tblMaintenance.mntVehicle ORDER BY tblMaintenance.mntDue ASC LIMIT 20; Again this kind of works, it brings up the next exam date for each vehicle, but shows the wrong values from tblExams, and I have no idea why! For reference tblMaintenance has the following fields: mntID = Primary Key mntVehicle = the vehicle on which it has been done - links to vclID in tblVehicles mntExam = the type of inspection that is being done - links to exmID in tblExams mntDue = with time sensitive exams, the date the exam is due. Null if the exam is not time-sensitive. mntDateCompleted = self-explanatory. tblExams has the following fields exmID = Primary Key exmTime = 1 if the exam is time-sensitive, 0 if it is based on number of days run. exmFrequency = Integer. Represents the number of days between exams, or months if it is time sensitive. exmTitle = What the type of exam is called. Any help would be greatly appreciated. Thanks Chris.
UPDATE - I only need an answer for the first query. The second one turns out not to be a problem (the six-monthly and twelve-monthly exams run out at around the same time, so it's actually helpful to display them both then nothing gets missed.