VBA code for Rank function - urgent - by class

[Krish]

Can anybody help me to write a user defined function in Access or
excel to rank scores of students please note if there is atie in score
for ex - C=20 and D=20
then C = rank and D = Rank + 1 and so on sequentially

I am looking to populate the Rank column

Ex score Rank Class
A 10 1 ABC
B 20 2 ABC
C 30 3 ABC
D 30 4 ABC
E 40 5 ABC
F 50 6 ABC
A 15 1 XYZ
B 21 3 XYZ
C 18 2 XYZ
D 30 4 XYZ
E 40 5 XYZ
F 50 6 XYZ

 

[Krish]

Can somebody please reply for my question

 

[BruceM]

This is a volunteer newsgroup. If you need an immediate response you should
consider paid consulting services (just to be clear, I am not offering).
Otherwise you should plan to wait at least 24 hours before asking the
question again. For more information about newsgrouip etiquette, see:
http://www.mvps.org/access/netiquette.htm

To the question at hand, try a Google groups search for information about
ranking records in Access. There are several options, including ranking
queries and numbering records on reports.

 

[John Spencer]

Why do you need this function - as in where are you going to use the value.
If you want to do this in a report it is very simple. If you need to do it in
a query it is more complex. AND you should not do it in a table.

In a report Group by Class and sort by Score.
In the detail section add a control
Name: txtRank
Control Source: = 1
Running Sum: Over Group

In a query doing this without ties is complex. If you allow ties then it is
fairly simple.

This would return 1,2,3,3,5,6 for the Class ABC and 1,3,2,4,5,6 for Class XYZ

SELECT Ex, Score, Count(B.Score)+1 as Rank, Class
FROM YourTable as A LEFT JOIN YourTable as B
ON A.Class = B.Class
AND A.Score > B.Score
GROUP BY Ex, Score, Class

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County