Display Variables and Sort by Integer

I have several select statements which return data.  I have to scrub the data quite a bit to get the new variables I need.  Let's say this is the data I have, with this as an example row:

ActDate = "Feb01"
ValOne = 10
ValTwo = 10
ValThree = ValOne + ValTwo

In a list, I could easily display the values like so:

Feb 01      10         20         30
Feb 02      08        04          12
Feb 03      03        15          18
Feb 04      01        04          05

And on and on.  What I want to do is to take these values that I have created and place them on a form in descending order.  How can I do that?  Thanks.
UPRRDevelopersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

butch_18445Commented:
>>What I want to do is to take these values that I have created and place them on a form in descending order.  How can I do that?

which values?  how are they stored and where.  are they in an array? in a file?
0
Arthur_WoodCommented:
what version of VB are you using?

AW
0
UPRRDevelopersAuthor Commented:
VB 6.  This is pretty straightforward.  I'm doing a SELECT and assigning variables as shown below:

PeopleCmd.CommandText = "SELECT USER_IDS FROM GROUPS WHERE GROUP_ID = 'SALES_REPS'"
Set GetSalesRepsRS = SalesCmd.Execute

GetSalesRepsRS.MoveFirst
   
    While (Not GetSalesRepsRS.BOF And Not GetSalesRepsRS.EOF)
        CurrentID = GetSalesRepsRS![USER_IDS]

        SalesCmd.CommandText = "SELECT COUNT(*) AS ""TKTCOUNT"" FROM TRANSFERS WHERE USER_ID = '" & CurrentID & "'"
        Set TransferredToSalesRepsRS = SalesCmd.Execute
        TransferredCounter = Int(TransferredToSalesReps![TKTCOUNT])

        SalesCmd.CommandText = "SELECT COUNT(*) AS ""TKTCOUNT"" FROM SALES WHERE STATUS <> 'FINISHED' AND USER_ID = '" & CurrentID & "'"
        Set OwnedBySalesRepsRS = SalesCmd.Execute

        OwnedCounter = Int(OwnedBySalesRepsRS![TKTCOUNT])
        TotalTickets = TransferredCounter + OwnedCounter
        lstTempList.AddItem TotalTickets & " " & CurrentID & " " & OwnedCounter & " " & TransferredCounter

        GetSalesRepsRS.MoveNext

    Wend

I get the values and assign the variables.  Now I want to create a list of what I found, ordered by the "TotalTickets" variable.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

butch_18445Commented:
what I would do is keep a counter and store them in an array.  I personally would make a TYPE with TotalTickets, CurrentID, OwnedCounter, and TransferredCounter, it makes the coding a bit cleaner.  then use the QuickSort alg to put it in order.  like this:


Private Type MyData
    TotalTickets       As Integer
    CurrentID          As Integer
    OwnedCounter       As Integer
    TransferredCounter As Integer
End Type
 
Private Sub Blah()
    
    Dim Ar() As MyData, i As Integer
    
    PeopleCmd.CommandText = "SELECT USER_IDS FROM GROUPS WHERE GROUP_ID = 'SALES_REPS'"
    Set GetSalesRepsRS = SalesCmd.Execute
    
    GetSalesRepsRS.MoveFirst
 
    While (Not GetSalesRepsRS.BOF And Not GetSalesRepsRS.EOF)
    
        ReDim Preserve Ar(i)
        
        Ar(i).CurrentID = GetSalesRepsRS![USER_IDS]
 
        SalesCmd.CommandText = "SELECT COUNT(*) AS ""TKTCOUNT"" FROM TRANSFERS WHERE USER_ID = '" & Ar(i).CurrentID & "'"
        Set TransferredToSalesRepsRS = SalesCmd.Execute
        Ar(i).TransferredCounter = Int(TransferredToSalesReps![TKTCOUNT])
 
        SalesCmd.CommandText = "SELECT COUNT(*) AS ""TKTCOUNT"" FROM SALES WHERE STATUS <> 'FINISHED' AND USER_ID = '" & Ar(i).CurrentID & "'"
        Set OwnedBySalesRepsRS = SalesCmd.Execute
 
        Ar(i).OwnedCounter = Int(OwnedBySalesRepsRS![TKTCOUNT])
        Ar(i).TotalTickets = Ar(i).TransferredCounter + Ar(i).OwnedCounter
        lstTempList.AddItem Ar(i).TotalTickets & " " & Ar(i).CurrentID & " " & Ar(i).OwnedCounter & " " & Ar(i).TransferredCounter
 
        GetSalesRepsRS.MoveNext
        i = i + 1
    Wend
    
    Call QuickSort(Ar, 0, i - 1)
 
End Sub
 
Private Sub QuickSort(ByRef sA() As MyData, Low As Long, High As Long)
 
    Dim sM As Long, medWord As String, sL As Long, sH As Long
 
    If Low < High Then
        sM = Int((Low + High) / 2)
        medWord = sA(sM).TotalTickets
        
        sL = Low
        sH = High
        
        Do
            While sA(sL).TotalTickets < medWord
                sL = sL + 1
            Wend
            While sA(sH).TotalTickets > medWord
                sH = sH - 1
            Wend
            
            If sL <= sH Then
                Call Swap(sA, sL, sH)
                sL = sL + 1
                sH = sH - 1
            End If
            
            DoEvents
            
        Loop Until sL > sH
        
        If sH <= sM Then
            Call QuickSort(sA, Low, sH)
            Call QuickSort(sA, sL, High)
        Else
            Call QuickSort(sA, sL, High)
            Call QuickSort(sA, Low, sH)
        End If
        
    End If
 
End Sub
 
Private Sub Swap(ByRef sA() As MyData, l As Long, H As Long)
 
    Dim Tmp As MyData
    
    Tmp.TotalTickets = sA(l).TotalTickets
    Tmp.CurrentID = sA(l).CurrentID
    Tmp.OwnedCounter = sA(l).OwnedCounter
    Tmp.TransferredCounter = sA(l).TransferredCounter
 
    
    sA(l).TotalTickets = sA(H).TotalTickets
    sA(l).CurrentID = sA(H).CurrentID
    sA(l).OwnedCounter = sA(H).OwnedCounter
    sA(l).TransferredCounter = sA(H).TransferredCounter
    
    sA(H).TotalTickets = Tmp.TotalTickets
    sA(H).CurrentID = Tmp.CurrentID
    sA(H).OwnedCounter = Tmp.OwnedCounter
    sA(H).TransferredCounter = Tmp.TransferredCounter
 
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
butch_18445Commented:
I threw your code in the sub Blah() because it needed a procedure.  at the bottom of it, after it calls QuickSort, the array will be in order.  Ar(0) is the lowest TotalTickets, and it goes up.
0
butch_18445Commented:
oh, and that of course is Ascending order, so if you want to show them in Descending, when you display them go in reverse in the array, like:
For i = UBound(Ar) To 0 Step -1
    List1.AddItem Ar(i).TotalTickets
Next

Open in new window

0
UPRRDevelopersAuthor Commented:
Thanks, Butch!  That's a great solution!
0
butch_18445Commented:
Thanks :)  I just wanted to add, that for the Swap function, I have no idea what I was thinking.  You can swap based on the Type alone like:
Private Sub Swap(ByRef sA() As MyData, l As Long, H As Long)
 
    Dim Tmp As MyData
    
    Tmp = sA(l)
    sA(l) = sA(H)
    sA(H) = Tmp
 
End Sub

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.