GroupBy / Max ? (newbie alert! :-) )

[Stephanie.D]

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

 

[macca the hacke]

Your theory is correct. I have replicated your tables and query, and using the max function only on the date gives the 3 expected results (see screen shot)

 

[Stephanie.D]

Wow, I am impressed, thanks for taking the time to re-create my database on your own computer. My company's paid IT people are not as thorough.

I see what my problem was. I had an extra date field that I did not include in my table description above.

The extra field was the date the course was taken. And if I understand this correctly, by specifying "GroupBy" on this field, it messed up the uniqueness of the EmplID-Name-CourseID-Course combination.

Rather than "GroupBy" is there some way to specify an "Ignore" option? ie, I want this extra field to come along for the ride and show up in the final results, but don't take it into consideration for grouping purposes.

Actually I may have just answered my own question - after typing the above paragraph, I changed GroupBy to Where and specified a data greater than #1/1/1900#. I don't really care about the date, but it is a way to get the GroupBy out of there.

Am I safe to do this or could it cause some sort of problem...

Thanks againnnn...

 

[Moniker]

Since you don't care what that second date value is, instead of forcing it to be an actual date, just make sure something is there. In other words, instead of WHERE SecondDate > #1/1/1900#, use WHERE SecondDate Is Not Null. That way, so long as something -- anything -- is in the SecondDate field, it's "coming along for the ride", so to speak.

~Moniker

 

[macca the hacke]

Can you not just remove the field?? If you are not using it later on, and are not querying it, then why have it in the query?

 

[Stephanie.D]

I do need that extra date field. When I said I don't care about it, I meant in terms of grouping. I want the report to show (most importantly) when the course is required again, but also, just as an fyi, when the course was last completed.

I will try the Is Not Null idea.

Thanks again. With service like this I'll be back and will tell my friends.