how do i do this, Ranking System

I have a database of names and scores. Heres how the list looks like:


Andy|55
Blah2|66
LOL|12
Shane|221
Mike|923
Gloria|222


The list has around 2,000 names|scores.

how can i make it set each user a rank by the score they have, like this:

Mike:Rank 1
Gloria:Rank 2
Shane:Rank3
Blah2:Rank4
Andy:Rank5
LOL:Rank6.

i want to seach for user Andy and have it  show a rank of #5.


andyakiraAsked:
Who is Participating?
 
Mike TomlinsonConnect With a Mentor Middle School Assistant TeacherCommented:
Here is one way.  Create a new project and add a ListBox and a TextBox.  Set the Sorted property of the ListBox to True.  The application expects you text file to be in the applications directory under the name of "scores.txt".  When the form loads it will processed into the ListBox and sorted.  Then, enter the name you want to find the rank for into the TextBox and press Enter.

Idle_Mind

Option Explicit

Private Sub Form_Load()
    Dim dataFile As String
    Dim inputLine As String
    Dim values As Variant
   
    dataFile = App.Path & "\scores.txt"
    If Dir(dataFile) <> "" Then
        List1.Clear
        Open dataFile For Input As #1
        While Not EOF(1)
            Line Input #1, inputLine
            values = Split(inputLine, "|")
            List1.AddItem Format(values(1), "000000000") & ", " & values(0)
        Wend
        Close #1
    Else
        MsgBox dataFile, vbCritical, "File not found"
    End If
End Sub

Private Sub Text1_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then
        KeyAscii = 0
       
        If Len(Text1.Text) > 0 Then
            Dim rank As Integer
            rank = getRank(Text1.Text)
            If rank <> -1 Then
                MsgBox Text1.Text & " is ranked #" & rank, vbExclamation, "Ranking"
            Else
                MsgBox Text1.Text, vbInformation, "Name not found"
            End If
        End If
    End If
End Sub

Private Function getRank(ByVal name As String) As Integer
    Dim i As Integer
    name = LCase(name)
   
    For i = 0 To List1.ListCount - 1
        If InStr(LCase(List1.List(i)), name) > 0 Then
            getRank = List1.ListCount - i
            Exit Function
        End If
    Next i
    getRank = -1
End Function

0
 
AzraSoundCommented:
If that's all you have to go by, I think youd have to do something like:


"SELECT * FROM myTable ORDER BY scores DESC"

Do
    i = i + 1
Loop Until recordset.Fields("name") = "Andy"

MsgBox "Rank = " & i
0
 
andyakiraAuthor Commented:
how does teh "SELECT * FROM  work?
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
andyakiraAuthor Commented:
is there another way of doing this without using sQL
0
 
AzraSoundCommented:
You are using a database, but dont want to use any SQL?
0
 
andyakiraAuthor Commented:
im not familiar with it, where can i go to learn more about sql?
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
Is your "database" just a text file?

If so, are we changing the file from this:

Andy|55
Blah2|66
LOL|12
Shane|221
Mike|923
Gloria|222

to this:

Mike:Rank 1
Gloria:Rank 2
Shane:Rank3
Blah2:Rank4
Andy:Rank5
LOL:Rank6

or do you want a second "database" text file containing this info?

Idle_Mind
0
 
andyakiraAuthor Commented:
The file is a txt file that contains names:scores, i'm not trying to convery anything. just trying to find the rank of the user based on their score.
0
 
andyakiraAuthor Commented:
Hello Idle_Mind,
I'm getting a script out of range "ScoreL.AddItem Format(values(1), "000000000") & ", " & values(0)". do you know whats causing it
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
If a line in your text file is missing a "|" between the name and the score that could cause it.

Try changing your While...Wend loop to this and see if that fixes it:

        While Not EOF(1)
            Line Input #1, inputLine
            If Instr(inputLine, "|") > 0 Then
                values = Split(inputLine, "|")
                List1.AddItem Format(values(1), "000000000") & ", " & values(0)
            End If
        Wend

Idle_Mind
0
 
andyakiraAuthor Commented:
Thanks idle mind
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.