Filter out records with earlier dates

  • Thread starter Thread starter SylviaL
  • Start date Start date
S

SylviaL

I have a data base for tracking mandatory training in an organization. Many
workshops must be taken each year. I have created a query that tracks expiry
dates but I can't figure out how to write an expression that filters out
dates of prior completed courses. For example, Joe takes a WHMIS course on 11
Jan 2007 which expired on 10 Jan 2008. He took a second course on 23 Jan
2008, which expires on 22 Jan 2009. I want the query to show the 2009 expiry
date but now show the 2008 expiry date, or any other WHMIS courses he has
completed in the past. We can't delete earlier records for tracking reasons
and prefer to filter out the uneeded information through a query.
 
It will be a query like this:
SELECT EmpID, CourseID, Max(Expiry) AS MaxOfExpiry
FROM Table1
GROUP BY EmpID, CourseID;
 
Back
Top