vba/sql to query table and return lowest 5 of 8 scores

[Golf3]

I have a table that basically has the following fields, Date, test_score and student ID. Date is the key field. I would like to write a vba or sql routine that would query the table and return the lowest 5 of the last 8 scores per student ID to a query. I'm stumped as to how to go about this. I'm asuming I would use Dlookup. It's been awhile since I have wrote code and I'm a little rusty. Thanks for any help.

 

[PHV]

A starting point (SQL code, typed untested).
The last 8 scores per student ID (qryLast8):
SELECT A.[student ID], A.Date, A.test_score
FROM yourTable AS A INNER JOIN yourTable AS B ON A.[student ID] = B.[student ID]
WHERE A.Date <= B.Date
GROUP BY A.[student ID], A.Date, A.test_score
HAVING Count(*) <= 8

The lowest 5 scores per student ID (using above saved qyery):
SELECT A.[student ID], A.Date, A.test_score
FROM qryLast8 AS A INNER JOIN qryLast8 AS B ON A.[student ID] = B.[student ID]
WHERE A.test_score >= B.test_score
GROUP BY A.[student ID], A.Date, A.test_score
HAVING Count(*) <= 5
ORDER BY 1, 3

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886