How do I assign a rank to records?

[Joey]

The following is a hypothetical example but relates to a database I am
attempting to develop. For this exercise, I have student results for
particular subjcts over several years. I have been able to sort the student
results so that for each subject in a given year the students are in
descending order according to their mark for that subject. To reach this
point I have a table of student results from which I have constructed the
following query which is sorted on, in order, Subject (Ascending) and Result
(Descending):

Year Subject Student Result (Rank)
1996 Maths John 90 ( 1 )
1996 Maths Bill 80 ( 2 )
1996 Maths Jack 70 ( 3 )
1996 Maths Bob 60 ( 4 )
1996 Science Jack 90 ( 1 )
1996 Science John 80 ( 2 )
1996 Science Bob 70 ( 3 )
1996 Science Bill 60 ( 4 )

I would now like to assign a rank to each record as shown in brackets. I
would like this rank to accompany this record as I do further analysis of my
data.

In Excel, I would use the RANK Function for this purpose. As yet I have not
been able to find an equivalent function in ACCESS 97.

Any help or guidance would be appreciated.

 

[Pieter Wijnen]

The following code ought to do the trick
-------------------------------------------------------
Public Function GetRank(ByVal Yr As Integer, ByVal Subject As String,
Student As String) As Integer
' Student is not actually used but needed because of "smart" behaviour
Static cYr As Integer
Static cSubject As Strin
Static Rank As Integer
Dim changed As Boolean
On Error Resume Next
changed = Not (cYr = Yr And cSubject = Subject) ' why we love de Morgan..
If changed Then
Rank = 1
cYr = Yr
cSubject = Subject
Else
Rank = Rank + 1
End If
GetRank = Rank
End Function
----------------------------------------------
Apply it to a query based on the sorted data

HTH

Pieter

 

[Joey]

Pieter,

Thanks for your response