Rank in Querry

daameta

Registered User.
Local time
Today, 06:58
Joined
May 26, 2015
Messages
25
Hello every one
i have a query "ranking" which have three field id, Name, percentage, Rank. the filed rank will displayed rank of student in class. if class have 16 students than rank must go "first" to "sixteenth" here is attached my screenshot. suggest me what to do to got rank. this query forward data to a report called "rpt1" so progress card show rank.






Screenshot 2016-04-29 11.57.16.pngScreenshot 2016-04-29 11.57.16.png
 
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(n)
Else
fnRankingName = GetTens(n)
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
 
Last edited:
does the original table where "ranking" query is derived has those fields also?
thanks for reply
i have two conditions
1- first is the original table have only Id, Name, Percentage Field. the field "rank in class" is new in my query. then how to got rank in my query.
2- if first condition is difficult or not possible then i will create a field named as rank in my table. now what is method to got rank in my table.
 
Last edited:
If you use the code above, that rank in class is calculated on the fly.
No need to save it or add extra code.
 
If you use the code above, that rank in class is calculated on the fly.
No need to save it or add extra code.
Sorry i Cannt understand your reply please tell me more. i am waiting for you.
 
thanks its working properly thanks a lot
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(n)
Else
fnRankingName = GetTens(n)
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
 
I just pointed that you don't need a new field in your table.
That was all :)
 

Users who are viewing this thread

Back
Top Bottom