Solved

how do i do this, Ranking System

Posted on 2004-09-11
11
222 Views
Last Modified: 2010-05-02
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.


0
Comment
Question by:andyakira
  • 6
  • 3
  • 2
11 Comments
 
LVL 28

Expert Comment

by:AzraSound
ID: 12037048
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
 

Author Comment

by:andyakira
ID: 12037069
how does teh "SELECT * FROM  work?
0
 

Author Comment

by:andyakira
ID: 12037137
is there another way of doing this without using sQL
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 28

Expert Comment

by:AzraSound
ID: 12037141
You are using a database, but dont want to use any SQL?
0
 

Author Comment

by:andyakira
ID: 12037189
im not familiar with it, where can i go to learn more about sql?
0
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 12037193
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
 

Author Comment

by:andyakira
ID: 12037227
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
 
LVL 85

Accepted Solution

by:
Mike Tomlinson earned 500 total points
ID: 12037293
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
 

Author Comment

by:andyakira
ID: 12037327
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
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 12037386
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
 

Author Comment

by:andyakira
ID: 12037421
Thanks idle mind
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Computer crashes, following error message in event manager 5 234
Using "ScreenUpdating" 6 73
Modifying Conditional Format from VBA code 3 59
Added a column screws up code 5 63
Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question