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.

 

[KenSheridan via AccessMonster.com]

Are you returning columns other than Name, Course and Date in the query? If
so that would account for the multiple rows. In that case you need to use a
subquery to get the latest date per person/course and use this to restrict
the rows returned, e.g.

SELECT *
FROM [YourTable] AS T1
WHERE T1.[Date] =
(SELECT MAX(T2.[Date])
FROM [YourTable] AS T2
WHERE T2.[Name] = T1.[Name]
AND T2.[Course] = T1.[Course]);

Note how the two instances of the table are given aliases T1 and T2 to
differentiate them and allow the subquery to be correlated with the outer
query on Name and Course.

If, on the other hand, you are returning only the three columns then grouping
the query by name and course and returning the MAX date as Lynn described is
the way to do it.

BTW Name and Date are not good column names as they are the names of the
built in Name property and Date function in access, so should be avoided.
Use more specific names like StudentName and CourseDate.

Ken Sheridan
Stafford, England

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

[quoted text clipped - 29 lines]