Most recent record

[Cameron]

I need to filter an employees training records based on the most recent
course taken. I have a number of employees that have taken extensive training
and have long employment histories. I can filter by what courses they have
taken but this brings up the complete list of all the courses over the years.
But I only want the most recent course not all the rest. The report also has
to show all the employees and their most recent attendence for a particular
course. How do I filter inside SQL so that only the most current course is
listed?

 

[Jeff Boyce]

Cameron

Use SELECT TOP 1 ...
.... and ORDER BY ...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Cameron]

oh. Will that not just give me one record? If I am filtering for say fire
extinguisher training from all the employees but I want to know who will be
expiring for their training this year. Will selecting the Top 1 from all the
employees leave me with just the most recent training for the employees? Or
will it just give me the only employee that fits the bill for being current?

Example:

Say I have bill who has taken training for the last 3 years, a course a year
and the courses expire every 3 months. And then I have Jim who also has taken
the same courses but has missed last year. By selecting using the top
qualifier will I get both records for Bill and Jim or just Bill?

 

[Jeff Boyce]

Cameron

Take a look at the GroupBy (Totals query). You can GroupBy Employee and
find the Max DateTaken for each.

Regards

Jeff Boyce
Microsoft Office/Access MVP