I apologize if this isn't exactly where this should be posted. I'm having trouble modifying a query in Access 2007. This is a training query that returns various training's for my companies employee list. More specifically, this query is used to determine whose training is nearing expiration. After multiple years of data entry, its become unreliable as it returns multiple dates even when filtered. What Id like to do is modify this so it would return only the most recent expiration date for a specific class. Here's what I've written:


SELECT EMPREQUIRED.[Job Title], [COURSE ENROLLMENT].etid, EMPREQUIRED.[Employee Name], EMPREQUIRED.[TYPID], [COURSE ENROLLMENT].[Course ID], EMPREQUIRED.[Course Name], [COURSE ENROLLMENT].[Expiration Date], "YES" AS Completed
FROM EMPREQUIRED INNER JOIN [COURSE ENROLLMENT] ON (EMPREQUIRED.CourseID = [COURSE ENROLLMENT].[Course ID]) AND (EMPREQUIRED.ETID = [COURSE ENROLLMENT].ETID)


UNION ALL

SELECT EMPREQUIRED.[Job Title], EMPREQUIRED.ETID, EMPREQUIRED.[Employee Name], EMPREQUIRED.[TYPID],EMPREQUIRED.CourseID, EMPREQUIRED.[Course Name], NULL, "NO" AS Completed
FROM EMPREQUIRED LEFT JOIN [COURSE ENROLLMENT] ON (EMPREQUIRED.CourseID = [COURSE ENROLLMENT].[Course ID]) AND (EMPREQUIRED.ETID = [COURSE ENROLLMENT].ETID)
WHERE ((([COURSE ENROLLMENT].ETID) Is Null))
ORDER BY ETID;