does the original table where "ranking" query is derived has those fields also?
anyway create a new query based on ranking query:
select id, name, percentage, fnRanking([id],"ranking") As [rank in Class] from ranking order by percentage DESC;
on a module, make a new function fnRanking:
Public Function fnRanking(lngID As Long, strQuery As String) As String
Dim db As dao.Database
Dim rs As dao.Recordset
Dim i As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("select * from " & strQuery & " order by [percentage] DESC;", dbOpenSnapshot)
With rs
If Not (.BOF And .EOF) Then
.MoveFirst
.FindFirst "[id] = " & lngID
If Not .NoMatch Then
While Not .BOF
i = i + 1
.MovePrevious
Wend
End If
End If
.Close
End With
Set rs = Nothing
Set db = Nothing
fnRanking = fnRankingName(i)
End Function
Public Function fnRankingName(ByVal n As Long) As String
If n < 10 Then
fnRankingName = GetDigit

Else
fnRankingName = GetTens

End If
End Function
' Converts a number from 10 to 99 into text.
Private Function GetTens(TensText)
Dim result As String
result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
Select Case Val(TensText)
Case 10: result = "Tenth"
Case 11: result = "Eleventh"
Case 12: result = "Twelveth"
Case 13: result = "Thirteenth"
Case 14: result = "Fourteenth"
Case 15: result = "Fifteenth"
Case 16: result = "Sixteenth"
Case 17: result = "Seventeenth"
Case 18: result = "Eighteenth"
Case 19: result = "Nineteenth"
Case Else
End Select
Else
' If value between 20-99...
If Val(Right(TensText, 1)) = 0 Then
Select Case Val(Left(TensText, 1))
Case 2: result = "Twentyth"
Case 3: result = "Thirtyth"
Case 4: result = "Fortyth"
Case 5: result = "Fiftyth"
Case 6: result = "Sixtyth"
Case 7: result = "Seventh"
Case 8: result = "Eightyth"
Case 9: result = "Ninetyth"
End Select
Else
Select Case Val(Left(TensText, 1))
Case 2: result = "Twenty "
Case 3: result = "Thirty "
Case 4: result = "Forty "
Case 5: result = "Fifty "
Case 6: result = "Sixty "
Case 7: result = "Seventy "
Case 8: result = "Eighty "
Case 9: result = "Ninety "
Case Else
End Select
End If
result = result & GetDigit _
(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = result
End Function
' Converts a number from 1 to 9 into text.
Private Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "First"
Case 2: GetDigit = "Second"
Case 3: GetDigit = "Third"
Case 4: GetDigit = "Fourth"
Case 5: GetDigit = "Fifth"
Case 6: GetDigit = "Sixth"
Case 7: GetDigit = "Seventh"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Ninth"
Case Else: GetDigit = ""
End Select
End Function