Most recent entries?

[uselessataccess]

Hi there,

I have been googling but can't find a solution to my problem.

I have a staff training table with a bunch of fields but the two of interest for this query are CourseID and CourseDate.

I want to keep a complete training record, with this in mind, when a member of staff completes a course they have already done 2 or 3 years ago, a new record is created with the most recent course details. (as opposed to just changing the dates on the existing table entry for the course 2 or 3 years ago)

HOWEVER, now it's been asked of me to produce various queries. For example, training that expires in the next 90 days or list of all current training for a member of staff.

Obviously, when I run a query with criteria of expiry date within 90 days or less at the moment I get ALL the previous courses, even if they have been superceeded by a more recent sitting of the same course. Ditto, if I query by staff member, I get 2 or 3 entries for the same course reflecting each and every time they took it.

So what I want to do is just return the most recent date for EACH course. I have been fiddling with the TOP function with SORT BY etc but I couldn't come with a way to combine them to achieve my goal.

Am I over complicating? Or is this particular functionality messy to do?

Thanks a million guys and gals.

Crisco

 

[Galaxiom]

Use an aggregate (aka Totals) query.
Right click on the designer grid and turn on Totals.

Set the Datefield to Max and Group By the others.