Qery help - get data1 for MAX of data2 for each of data3

[Guest]

I need a query that will get the latest LEVEL for each student. The way I
think we can do it is:

get Level_At_Date
for MAX of Record_ID
for each Student_ID

The table looks like this:
Table is called student_level_history

Record_ID Student_ID Level_at_Date
1957 12 AQ7
2947 14 AQ5
9617 14 SK5
1069 16 AQ5
3531 16 SK4
5528 16 SK4
6931 16 SK4
7284 16 SK4
1144 17 AQ2
3530 17 SK1
5476 17 SK1
6917 17 SK2
7228 17 SK3

I am not sure how to code this... any help greatly appreciated.

thanks
sandy

 

[Guest]

Try this using two queries (if you know subqueries it can be done in one.
Sandy_1 ---
SELECT Sandy.Student_ID, Max(Sandy.Record_ID) AS MaxOfRecord_ID
FROM Sandy
GROUP BY Sandy.Student_ID;

SELECT Sandy.Record_ID, Sandy.Student_ID, Sandy.Level_at_Date
FROM Sandy INNER JOIN Sandy_1 ON (Sandy.Student_ID = Sandy_1.Student_ID) AND
(Sandy.Record_ID = Sandy_1.MaxOfRecord_ID);

 

[Guest]

Worked like a charm - THANK YOU!!

san

Try this using two queries (if you know subqueries it can be done in one.
Sandy_1 ---
SELECT Sandy.Student_ID, Max(Sandy.Record_ID) AS MaxOfRecord_ID
FROM Sandy
GROUP BY Sandy.Student_ID;

SELECT Sandy.Record_ID, Sandy.Student_ID, Sandy.Level_at_Date
FROM Sandy INNER JOIN Sandy_1 ON (Sandy.Student_ID = Sandy_1.Student_ID) AND
(Sandy.Record_ID = Sandy_1.MaxOfRecord_ID);