Make ranking in a report

  • Thread starter Thread starter ACE Connection
  • Start date Start date
A

ACE Connection

I have made a report that prints out athletes in ascending order with their
score. In Excel it is quite easy to make a column with ranking that also
takes into account athletes with same score will have the same rank, and the
next in rank will be numbered two lower. For example: A have 20
points=number one, B have 20 points=number one, C have 19 points=number
three.



Does anyone have a solution to this?



Thank you all in advance..



Espen
 
ACE said:
I have made a report that prints out athletes in ascending order with their
score. In Excel it is quite easy to make a column with ranking that also
takes into account athletes with same score will have the same rank, and the
next in rank will be numbered two lower. For example: A have 20
points=number one, B have 20 points=number one, C have 19 points=number
three.
Click to expand...

Typically this is done in the report's record source query
by using a subquery:

SELECT *,
(SELECT Count(*) + 1 AS Rank
FROM reportquery AS T
WHERE T.score < A.score)
FROM reportquery AS A
 
You can create an expression in the query:
Rank: (Select Count(*) From tblYourTable t where T.Points >
tblYourTable.Points) +1
 
Back
Top