Stephanie.D
New member
- Local time
- Today, 14:32
- Joined
- Dec 28, 2006
- Messages
- 6
I have what is probably a simple question for regular Access users. Before I ask the question, here is a simplified version of the tables involved:
Structure of EMPLOYEE
=====================
EmplID
Name
Structure of TRAINING
=====================
EmplID
CourseID
CourseName
DateTaken
Data in EMPLOYEE
================
EmplID----Name
1234------John Smith
5678------Mike Smith
Data in TRAINING
================
EmplID------CourseID----Course------DateTaken
1234--------NS01--------HeatStreet--06/15/2000
1234--------NS01--------HeatStreet--07/11/2001
1234--------NS01--------HeatStreet--02/07/2002
1234--------NS01--------HeatStreet--08/22/2004
1234--------NS01--------HeatStreet--01/28/2006
1234--------NS27--------Lockout-----01/06/2002
1234--------NS27--------Lockout-----01/27/2004
5678--------NS01--------HeatStreet--12/27/2002
5678--------NS01--------HeatStreet--08/11/2004
Its easy enough to join the two tables in the query and return ALL 9 training records, but I would like to find a way to display only the most recent occurance of each employee taking a course. ie, the max date for each unique EmplID-Name-CourseID-Course combination:
EmplID------Name--------CourseID----Course------DateTaken
1234--------John Smith--NS01--------HeatStreet--01/28/2006
1234--------John Smith--NS27--------Lockout-----01/27/2004
5678--------Mike Smith--NS01--------HeatStreet--08/11/2004
I thought maybe "GroupBy" on EmplID, Name, CourseID and Course and "Max" on DateTaken would do the trick, but apparently not. Is there a way to do this without code?
Thanks in advance........
-SD
Structure of EMPLOYEE
=====================
EmplID
Name
Structure of TRAINING
=====================
EmplID
CourseID
CourseName
DateTaken
Data in EMPLOYEE
================
EmplID----Name
1234------John Smith
5678------Mike Smith
Data in TRAINING
================
EmplID------CourseID----Course------DateTaken
1234--------NS01--------HeatStreet--06/15/2000
1234--------NS01--------HeatStreet--07/11/2001
1234--------NS01--------HeatStreet--02/07/2002
1234--------NS01--------HeatStreet--08/22/2004
1234--------NS01--------HeatStreet--01/28/2006
1234--------NS27--------Lockout-----01/06/2002
1234--------NS27--------Lockout-----01/27/2004
5678--------NS01--------HeatStreet--12/27/2002
5678--------NS01--------HeatStreet--08/11/2004
Its easy enough to join the two tables in the query and return ALL 9 training records, but I would like to find a way to display only the most recent occurance of each employee taking a course. ie, the max date for each unique EmplID-Name-CourseID-Course combination:
EmplID------Name--------CourseID----Course------DateTaken
1234--------John Smith--NS01--------HeatStreet--01/28/2006
1234--------John Smith--NS27--------Lockout-----01/27/2004
5678--------Mike Smith--NS01--------HeatStreet--08/11/2004
I thought maybe "GroupBy" on EmplID, Name, CourseID and Course and "Max" on DateTaken would do the trick, but apparently not. Is there a way to do this without code?
Thanks in advance........

-SD