How can I get up to the last 2 details for each master record?

Question

I need a query in MS-Access that will return up to the last to classes each student has completed. the data looks something like this:

Students

 ID  | StudentName
 1     John
 2     Bill
 3     Ted
 4     Edward

TrainingDetails

   ID   | StudentID  | ClassName | Date
   1      1            Math        10/10/2012
   2      1            Science     12/10/2012
   3      2            Math        10/10/2012
   4      3            Math        10/10/2012
   5      2            Art         09/10/2012
   6      2            History     02/10/2012
   7      3            Science     12/10/2012
   8      3            History     02/10/2012
   9      4            Music       12/10/2012 

Desired Output

Name   | Class     | Date 
John     Science     12/10/2012
John     Math        10/10/2012
Bill     Math        10/10/2012
Bill     Art         09/10/2012
Ted      Science     12/10/2012
Ted      Math        10/10/2012
Edward   Music       12/10/2012

I've tried using the SELECT TOP 2 clause, but i only get 2 records total. I think i need some type of loop to get each student and then the top 2 records for each student, but I can't get it all in a single query.

Answer 1

Try the following query. I have a working fiddle with SQLServer but should work for Access. You can order the result as per your need.

SELECT s.StudentName, t.classname, t.date
FROM Students s
    INNER JOIN TrainingDetails t ON t.StudentID = s.id
WHERE t.Date in (SELECT TOP 2 t2.Date 
                 FROM TrainingDetails t2 
                 WHERE t2.StudentID = s.ID 
                 ORDER BY t2.Date DESC)

(Code corrected to work with Access SQL.)