ranked report?

  • Thread starter Thread starter RichD
  • Start date Start date
R

RichD

I need to modify an existing report that lists student
names and test scores in descending order with the highest
test score first. I want to insert a ranking field either
in the query or on the report that would identify the
students rank based off of their score. Students with the
same score would need to have the same ranking number as
follows:
Rank Student Score
1 John Smith 625
2 Mary Smith 610
3 John Doe 595
3 Jane Jones 595
4 John Jones 578

I have everything in place except for the ranking field.
Any ideas? Thanks in advance for your help. Rich
 
RichD said:
I need to modify an existing report that lists student
names and test scores in descending order with the highest
test score first. I want to insert a ranking field either
in the query or on the report that would identify the
students rank based off of their score. Students with the
same score would need to have the same ranking number as
follows:
Rank Student Score
1 John Smith 625
2 Mary Smith 610
3 John Doe 595
3 Jane Jones 595
4 John Jones 578
Click to expand...

The last one should be ranked 5, not 4.

You can use a subquery in a calculated field to do this.

SELECT table.Srudent, table.Score,
(SELECT Count(*) + 1
FROM table As X
WHERE X.Scode < table.Score
) As Rank
FROM table
 
Back
Top