Select latest record for each Employee and Course

[AccessPower]

Happy Holidays to everyone!

So I have a sql query that pulls training dates for all employees and the courses they've completed. The query pulls a refresh rate of the course, the date the employee completed the course, and then calculates the expiration date based on that data.

What I am trying to do is to only get the latest completed training date. For instance, John completed his CPR Training annually in 2014, 2015, and 2016. I only want to see the record from 2016.



Here is the query so far:

SELECT tblEmployees.FullName, tblCourses.Course, tblCourses.RefreshRate, tblEmployeeTrainingTracker.LastCompleted, Switch(RefreshRate='Annual', DateAdd("yyyy",1,LastCompleted), RefreshRate='2 Year', DateAdd("yyyy",2,LastCompleted), RefreshRate='3 Year', DateAdd("yyyy",3,LastCompleted), RefreshRate='5 Year', DateAdd("yyyy",5,LastCompleted)) AS CourseExpires

FROM (tblCourses INNER JOIN tblEmployeeTrainingTracker ON tblCourses.[ShipsID] = tblEmployeeTrainingTracker.[ShipsID]) 
INNER JOIN tblEmployees ON tblEmployeeTrainingTracker.[EmployeeID] = tblEmployees.[EmployeeID] WHERE tblCourses.RefreshRate <> 'None' 

ORDER BY tblEmployees.FullName, tblCourses.Course;

I tried using the max function on tblEmployeeTrainingTracker.LastCompleted, but it just errors out. I am going to keep trying, maybe with some nested stuff, but I figured I'd post on here to colab and get any advice from the gurus.

Thanks for everything you guys do.

[JoeM]

I will tell you how you can do it in a series of two queries. If you like, you can then imbed the one into the other afterwards, if you prefer one nested query.

First, create a query that has FullName, Course, and LastCompleted date.
Then, click the Totals button to make it an Aggregate Query, and switch the Totals row value of "Group By" under the LastCompleted date to "Max" (leave the other two "Group By" values alone).
This will now give you the last completed date for each employee/course combination.

If you want to see more details (the other fields), create a new query that links this query back to your original tables, being sure to include the LastCompleted date in your join, and return any fields that you want.

[AccessPower]

Joe,

Thank you very much for your help. This was really a unique challenge for me. I did some slight modification to your advice. I ended up having to include the RefreshRate within the first query. Other than that it works perfectly. Here are the two queries that I used:

SELECT tblEmployees.Name, tblCourses.Course, tblCourses.RefreshRate, Max(tblEmployeeTrainingTracker.LastCompleted) AS MaxOfLastCompletedFROM (tblCourses INNER JOIN tblEmployeeTrainingTracker ON tblCourses.[ShipsID] = tblEmployeeTrainingTracker.[ShipsID]) INNER JOIN tblEmployees ON tblEmployeeTrainingTracker.[EmployeeID] = tblEmployees.[EmployeeID]
GROUP BY tblEmployees.Name, tblCourses.Course, tblCourses.RefreshRate
HAVING (((tblCourses.RefreshRate)<>'None'))
ORDER BY tblEmployees.Name, tblCourses.Course, tblCourses.RefreshRate, Max(tblEmployeeTrainingTracker.LastCompleted);

SELECT qryLatestTraining.Name, qryLatestTraining.Course, qryLatestTraining.MaxOfLastCompleted, Switch(qryLatestTraining.RefreshRate='Annual',DateAdd("yyyy",1,MaxOfLastCompleted),qryLatestTraining.RefreshRate='2 Year',DateAdd("yyyy",2,MaxOfLastCompleted),qryLatestTraining.RefreshRate='3 Year',DateAdd("yyyy",3,MaxOfLastCompleted),qryLatestTraining.RefreshRate='5 Year',DateAdd("yyyy",5,MaxOfLastCompleted)) AS CourseExpires
FROM qryLatestTraining;

This worked out perfectly. No errors.

Thank you for the mentoring once again.

[JoeM]

You are welcome!