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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
butch_18445Connect With a Mentor Commented:
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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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
All Courses

From novice to tech pro — start learning today.