Latest occurrence for same person

[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

 

[NG]

Hi,

you can create a groups query grouped bij person and course and show the
max(date)

gr
NG

 

[apollo11_1969]

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

 

[Jerry Whittle]

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.

 

[apollo11_1969]

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

 

[Lynn Trapp]

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

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

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.