Find lowest 5 of the last 8 scores results in new query

[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 . I would like the results to be stored in a query. I'm stumped as to how to go about this. It's been awhile since I have written complex code.

[geekgirlau]

Write a query that sorts the table by score, then change the query properties to set "Top Values" to 5.

[OBP]

I think that you might have to use geekgirlau's answer twice if you do not already have the last 8 scores.
i.e. sort the table first by Score Date in Descending Order selecting the first 8 Dates and then create a query based on that query to sort by scores.
I think it may only work with one studentID at a time, although you could try Grouping by StudentID.

[asingh]

Hi,

Attached is a sample Data base which does the job....

"tbl_Student_Data" has the data for the students. After the code executes "tbl_Lowest_Scores_8" will hold the lowest 8 scores and names, and "tbl_Lowest_Scores_5" will hold the lowest 5 scores and names.

Logic used:
1. Select All records from tbl_Student_Data sorted by score.
2. Pick up the first eight records and INSERT them into tbl_Lowest_Scores_8.
3. Select All records from tbl_Lowest_Scores_8 sorted by score.
4. Pick up the first five records and INSERT them into tbl_Lowest_Scores_5.

The Macro "mcr_sort_for_lowest_5" will do all the steps...!

Hope this helps...

regards,

asingh

[asingh]

Hi,

Attached is a sample Data base which does the job....

"tbl_Student_Data" has the data for the students. After the code executes "tbl_Lowest_Scores_8" will hold the lowest 8 scores and names, and "tbl_Lowest_Scores_5" will hold the lowest 5 scores and names.

Logic used:
1. Select All records from tbl_Student_Data sorted by score.
2. Pick up the first eight records and INSERT them into tbl_Lowest_Scores_8.
3. Select All records from tbl_Lowest_Scores_8 sorted by score.
4. Pick up the first five records and INSERT them into tbl_Lowest_Scores_5.

The Macro "mcr_sort_for_lowest_5" will do all the steps...!

Hope this helps...

regards,

asingh

[omocaig]

this worked for me:


SELECT *
FROM tblStudentScore
WHERE score in
(
select top 5 score from tblStudentScore as topnscore
where
topnscore.studentid = tblStudentscore.studentid
and topnscore.testdate in (
SELECT TOP 8 x.testDate
FROM tblStudentScore as x
where x.studentID = topnscore.studentID
ORDER BY x.testDate DESC
)
order by score);
hth,
Giacomo

[OBP]

Giacomo, now that is a real neat piece of SQL.

[omocaig]

Giacomo, now that is a real neat piece of SQL.
Thanks! I have seen some of your posts on this forum and you know your stuff... I appreciate the compliment. :beerchug:

Giacomo