Latest occurrence for same person

  • Thread starter Thread starter apollo11_1969
  • Start date Start date
A

apollo11_1969

Hi. I have db with a number of people and they do a certain type of a course,
but they have to have refresher courses every couple of years. How do I get
a list that has one occurrence of each name with the last time they took the
course? e.g.
what I have:
Name Course Date
Joe 1A 01/05/2006
Mary 1A 05/08/2007
Joe 1A 05/08/2007
Rich 1A 07/04/2001
Mary 1A 06/07/2009

What I want:
Joe 1A 05/08/2007
Mary 1A 06/07/2009
Rich 1A 07/04/2009
 
Hi. Thanks NG. What is a 'groups query'. Looked it up in help and in my
Access for dummies book, but cdn't find it. FYI I'm on Access 2003.

Apollo11_1969
 
Check out Totals queries in Help. If you look at the SQL statement for a
Totals query, you'll see a "Group By" clause. That's probaby why NG called it
a 'groups' query.
 
Hi Jerry. Thanks. Yes, I had tried a variety of max/last etc, in a no. of the
query fields - e.g. I put in 'last' in the date field, but there are still
multiple occurrences for each person when I run query. NG hadn't said what to
group by though [for the person & course]. There needs to be something that
links the date to the person I guess, so that it's the most recent date [for
that course] for that employee.

Thanks
Apollo11_1969
 
Switch your query to SQL View and past the following in the editor. Change
the Field Names and Table Names appropriately.

SELECT PersonName, Course, Max(CourseDate) AS LastDate
FROM YourTable
GROUP BY PersonName, Course;
--
Lynn Trapp
MCP, MOS, MCAS


apollo11_1969 said:
Hi Jerry. Thanks. Yes, I had tried a variety of max/last etc, in a no. of the
query fields - e.g. I put in 'last' in the date field, but there are still
multiple occurrences for each person when I run query. NG hadn't said what to
group by though [for the person & course]. There needs to be something that
links the date to the person I guess, so that it's the most recent date [for
that course] for that employee.

Thanks
Apollo11_1969

Jerry Whittle said:
Check out Totals queries in Help. If you look at the SQL statement for a
Totals query, you'll see a "Group By" clause. That's probaby why NG called it
a 'groups' query.
Click to expand...
Click to expand...
 
Back
Top