Qry Build: Last Class Taken

[md57780]

I'm having trouble designing a query in MCASSESS to generate the results I need. Can anyone provide any insight into how I need to build this correctly?

I have three tables relating to courses employees can take. Table1 is a list of courses, Table2 is a list of dates that courses were offered, and Table3 is a list of users registered for the course, and the recorded score.

I need to be able retrieve a list of employees that have taken a particular course (CourseID) and their associated score. Employees may take the course more than once. I only need the most recent date.

The data for Table3 is entered manually, and may not be entered chronologically.



Table1: CourseID, CourseName
Table2: ClassID, CourseID, ClassDate
Table3: RegID, EmpID, ClassID, Score



Sample Data for Table 1
1 - Sample Class 1
2 - Sample Class 2

Sample Data for Table2
7 - 1 - 4/1/2011
8 - 1 - 4/5/2011
9 - 2 - 4/3/2011

Sample Data for Table3
2 - MD57780 - 8 - 90
3 - MD57780 - 7 - 70
4 - MD57780 - 9 - 80



Sample Results for Course 1
MD57780 - Sample Class 1 - 4/5/2011 - 90



I don't necessarily need someone to "build" this query (although I wouldn't object to a working example), but I would like a little help into the direction I need to go to generate the results I need.

Any help at all would be appreciated.

Thanks!

 

[md57780]

The closest I could come is the following:

PARAMETERS [Course ID] Long;
SELECT QRY1.EmpID, Cou2.CourseName, QRY1.[Class Date], Reg2.Score
FROM (SELECT Reg1.EmpID, Max(Cla1.ClassDate) AS [Class Date] 
FROM Table3 AS Reg1 
INNER JOIN Table2 AS Cla1 ON Reg1.ClassID=Cla1.ClassID 
WHERE (((Cla1.CourseID)=[Course ID])) 
GROUP BY Reg1.EmpID)  AS QRY1 
INNER JOIN Table1 AS Cou2 
INNER JOIN (Table3 AS Reg2 
INNER JOIN Table2 AS Cla2 ON Reg2.ClassID = Cla2.ClassID) 
ON Cou2.CourseID = Cla2.CourseID) 
ON (QRY1.[Class Date] = Cla2.ClassDate) AND (QRY1.EmpID= Reg2.EmpID);

I've nested an aggregated query to find the last date an employee took a course, then created multiple joins against the date and EmpID to get the corresponding score.

It seems to work with a small dataset, but doesn't seem like the most efficient way to do this. Is there a better, more efficient method?

 

[jzwp22]

I was going to suggest a similar approach but with a series of queries (you can do it with nested queries, of course). If you don't want to specify the course via a parameter (i.e. get the score for each latest employee/course combination), you would have to include it in the aggregate query and join the aggregate query back using the 3 fields (empID, courseID and max date). I've attached a sample DB with the series of queries. qryFinal shows the final result set.

You can then pull information from the final query as needed.

 

[md57780]

Greatly appreciate the time you took to provide a working model! I'll be going through it here shortly.

BTW, good to see H.P. passed his classes.