Ranking of Child Records according to Groups

Imagine you have a school full of Students and they have done a variety of exams. All results are collected in a table and you would like to obtain rankings by subject. How can you automatically rank all the students for whom you have results.

The table T01Student
PKID Students Marks Subject
1 Tony 34 Maths
2 Bob 32 Maths
3 Thor 48 Maths
4 Jack 42 Geography
5 Tom 41 Geography
6 Kate 45 Geography
7 Sid 26 Geography
8 Michael 40 Chemistry
9 Colin 50 Chemistry
10 Hannah 60 Chemistry
11 Geoff 5 Chemistry
12 Jim 2 Chemistry

It is then possible to use the following query to get a ranking
SELECT (select count(*) from T01Student as tbl2 where T01Student.marks < tbl2.marks and T01Student.subject = tbl2.subject)+1 AS rank, * INTO TempRank FROM T01Student;

rank PKID Students Marks Subject
2 1 Tony 34 Maths
3 2 Bob 32 Maths
1 3 Thor 48 Maths
2 4 Jack 42 Geography
3 5 Tom 41 Geography
1 6 Kate 45 Geography
4 7 Sid 26 Geography
3 8 Michael 40 Chemistry
2 9 Colin 50 Chemistry
1 10 Hannah 60 Chemistry
4 11 Geoff 5 Chemistry
5 12 Jim 2 Chemistry

Then use a simple select query to order by subject then rank - Note Depending if you want to count down from the top so the lowest “Marks” gets the highest rank reverse the < symbol or reverse the order of rank - here I have highest mark is no 1.

Subject rank Students Marks
Chemistry 1 Hannah 60
Chemistry 2 Colin 50
Chemistry 3 Michael 40
Chemistry 4 Geoff 5
Chemistry 5 Jim 2
Geography 1 Kate 45
Geography 2 Jack 42
Geography 3 Tom 41
Geography 4 Sid 26
Maths 1 Thor 48
Maths 2 Tony 34
Maths 3 Bob 32

If for some reason you are wanting to store the rank so that you can artificially alter the ranking then it would be possible to use make table to create a new table with the ranking and then update a position field with the rank in the ranking query based on the PKID

About Mark

Mark Brooks a forty something individual working and living in and around Edinburgh
This entry was posted in All, SQL MS Access, VBA Code MS Access. Bookmark the permalink.