Generate Report that list current training course only

[TheHarleygirl2005]

I need to generate a training report that only lists the most current training records. In my TrainingT I have the following fields

EmployeeID
CourseDate
CourseType


CourseName
ExipirationDate
TrainingYear

Many of these courses are annual courses so there will be a new listing in the table for each year, but I need my report to only pull the most recent courses for each employee. Each employee would have several courses per report (i.e, Medical Course, Defensive Driving Course, etc.).

I'm looking for direction on how to make this happen. Can anyone help?

[June7]

One way is with TOP n parameter. Review: http://allenbrowne.com/subquery-01.html#TopN

[TheHarleygirl2005]

The TopN did not help. I have a query (MandatoryTrainingQ) that only lists Mandatory Training



SELECT TrainingT.[MIS#], TrainingT.CourseDate, TrainingT.CourseType, TrainingT.CourseID, CourseNameT.CourseDescription, TrainingT.CourseName, TrainingT.[Certification#], TrainingT.ExpirationDate
FROM TrainingT INNER JOIN CourseNameT ON TrainingT.CourseID = CourseNameT.CourseID
WHERE (((CourseNameT.CourseDescription)<>"Other"));


This query only displays my mandatory training courses for each member. If it is an elective course I select "Other" from my CourseDescription (which is filtered out)Now I need it to only pull the maximum date. I'm thinking using the Max() function might work, but I have never done that before and can't seem to get it right. Any suggestions?

[June7]

Why didn't TOP N work, what happened?

Try this:

If you want the max date for each member for each course:
Select MemberID, CourseID, Max(CourseDate) AS MaxCourseDate FROM TrainingT GROUP BY MemberID, CourseID;

Now join that query to TrainingT by linking on the MemberID and CourseID fields. Use criteria under CourseDate field: =[MaxCourseDate]

[TheHarleygirl2005]

I probably just don't know what I'm doing with the TOP N. This is what I have and it does not return any records.

SELECT TrainingT.[MIS#], TrainingT.CourseDate, TrainingT.CourseType, TrainingT.CourseID, CourseNameT.CourseDescription, TrainingT.CourseName, TrainingT.[Certification#], TrainingT.ExpirationDate
FROM TrainingT INNER JOIN CourseNameT ON TrainingT.CourseID = CourseNameT.CourseID
WHERE (((CourseNameT.CourseDescription)<>"Other")) IN
(SELECT TOP 1 CourseDate
FROM TrainingT
WHERE TrainingT.CourseID = CourseNameT.CourseID
ORDER BY CourseDate DESC, AttendanceID DESC)

[June7]

Did the other approach work?

I am not sure because can't test your data but the TOP N should be more like:

SELECT TrainingT.*, CourseDescription
FROM TrainingT INNER JOIN CourseNameT ON TrainingT.CourseID = CourseNameT.CourseID
WHERE CourseNameT.CourseDescription<>"Other" AND AttendanceID IN
(SELECT TOP 1 AttendanceID FROM TrainingT AS Dupe
WHERE Dupe.CourseID = TrainingT.CourseID AND
Dupe.EmployeeID = TrainingT.EmployeeID
ORDER BY Dupe.CourseDate, DESC);

[TheHarleygirl2005]

I have tried playing with this code where I have a "MemberID" rather than "EmployeeID" as you it have but I keep getting a Syntax error



Here is my updated code:

SELECT TrainingT.*, CourseDescription
FROM TrainingT INNER JOIN CourseNameT ON TrainingT.CourseID = CourseNameT.CourseID
WHERE CourseNameT.CourseDescription<>"Other" AND AttendanceID IN
(SELECT TOP 1 AttendanceID FROM TrainingT AS Dupe
WHERE Dupe.CourseID = TrainingT.CourseID AND
Dupe.MemberID = TrainingT.MemberID
ORDER BY Dupe.CourseDate, DESC);