I have a vehicle database with the vehicles in a table and a separate table containing preventative maintenance records “PM” and I want to display the last “pm” record for each unit filtered to overdue records.
The following custom query returns the last date but the corresponding fields don’t match. I can’t figure out what I am doing wrong.
SELECT MAX(milo.PmDueDate) AS due, Units.UNIT, Units.Type, milo.Rec
FROM Units
INNER JOIN PmOdometer AS milo on (milo.Unit = Units.unit)
WHERE Units.OOSDate IS null AND milo.PmDueDate < “{{NOW}}” AND Units.MaintBy = “Shop”
GROUP BY milo.Unit
ORDER BY milo.Unit ASC