Most recent record

C

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?
 
J

Jeff Boyce

Cameron

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

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
C

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?
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top