?
Solved

how do i do this, Ranking System

Posted on 2004-09-11
11
Medium Priority
?
226 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 86

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 86

Accepted Solution

by:
Mike Tomlinson earned 2000 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 86

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month14 days, 17 hours left to enroll

770 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