Solved How to sort values (the highest first)? If duplicates values found, the sorting is based on the saved table for those values

[devtr]

How do I sort the values in descending order?
Also, if two or more values are duplicates, the order depends on another table.

The table below is not saved in the database. The VBA code calculates and brings values for the member_credits column for each member ID.

member_id	member_credits
1​	0.7245​
2​	15.6285​
3​	28.7201​
4​	28.7201​
5​	9.9832​

I need to sort member_id in descending order based on the member_credits. So, the highest value (member_credits) is the first, and so on.
member_id 3 and 4 are duplicates. They will be ordered based on another table:

The table below is saved in the database.
tbl_credit_order

order_no

2​

3​

1​

5​

4​

So, the order for member_id will be as follows:

sort_id	member_id
1​	3​
2​	4​
3​	2​
4​	5​
5​	1​

Since member_id 3 and 4 have the same values, it looks for the order in the tbl_credit_order table. Where 3 comes first.
So,

Public Function GetComparisonArray(ByVal Member_Order_Number As Integer, ByVal M1 As Double, M2 As Double, M3 As Double, _
                                    M4 As Double, M5 As Double) As Integer

Dim ArrInput1() As Variant, ArrInput2() As Variant, arrResult() As Double
Dim iC As Integer, jC As Integer
Dim iResult As Integer

Dim MC1 As Double, MC2 As Double, MC3 As Double, MC4 As Double, MC5 As Double
Dim i As Integer

ArrInput1 = Array(M1, M2, M3, M4, M5)
ArrInput2 = Array(M1, M2, M3, M4, M5)

' 5 * 5 = 25 array (results)
ReDim arrResult(24)

' number to be used to hold each result in arrResult array (0 to 24)
iResult = -1

For iC = 0 To 4

    For jC = 0 To 4
    
        iResult = iResult + 1
    
        ' check if iC value is bigger than the rest of the jC values
        arrResult(iResult) = IIf(ArrInput1(iC) > ArrInput2(jC), 1, 0)
        
    Next jC
Next iC

For i = 0 To 4

    MC1 = MC1 + arrResult(i)

Next i

For i = 5 To 9

    MC2 = MC2 + arrResult(i)

'    Debug.Print arrResult(i)
Next i

For i = 10 To 14

    MC3 = MC3 + arrResult(i)

Next i

For i = 15 To 19

    MC4 = MC4 + arrResult(i)

Next i

For i = 20 To 24

    MC5 = MC5 + arrResult(i)

Next i

Select Case Member_Order_Number
        Case 1
            If MC1 = 4 Then GetComparisonArray = 1
            If MC2 = 4 Then GetComparisonArray = 2
            If MC3 = 4 Then GetComparisonArray = 3
            If MC4 = 4 Then GetComparisonArray = 4
            If MC5 = 4 Then GetComparisonArray = 5
Case 2
            If MC1 = 3 Then GetComparisonArray = 1
            If MC2 = 3 Then GetComparisonArray = 2
            If MC3 = 3 Then GetComparisonArray = 3
            If MC4 = 3 Then GetComparisonArray = 4
            If MC5 = 3 Then GetComparisonArray = 5
Case 3
            If MC1 = 2 Then GetComparisonArray = 1
            If MC2 = 2 Then GetComparisonArray = 2
            If MC3 = 2 Then GetComparisonArray = 3
            If MC4 = 2 Then GetComparisonArray = 4
            If MC5 = 2 Then GetComparisonArray = 5
Case 4
            If MC1 = 1 Then GetComparisonArray = 1
            If MC2 = 1 Then GetComparisonArray = 2
            If MC3 = 1 Then GetComparisonArray = 3
            If MC4 = 1 Then GetComparisonArray = 4
            If MC5 = 1 Then GetComparisonArray = 5
Case 5
            If MC1 = 0 Then GetComparisonArray = 1
            If MC2 = 0 Then GetComparisonArray = 2
            If MC3 = 0 Then GetComparisonArray = 3
            If MC4 = 0 Then GetComparisonArray = 4
            If MC5 = 0 Then GetComparisonArray = 5
End Select

End Function

This function works only when there are no duplicates. It returns the order number when used as a parameter in the function. And it returns the corresponding member ID.

I used this function in the Immediate Window:
?GetComparisonArray(3,0.7245,15.6285,28.7202,28.7201,9.9832)
and it returns 3
But if I change the values and make duplicates, this function will not work properly.
The sort_order table is used only when there are duplicate member_credits. It will apply to only those members.

If member 3 and 4 have same credit, the table tbl_credit_sort will be used to find out which member will be first between members 3 and 4.

I have attached Access file as well.

Maybe I am overthinking the solution and have made it difficult.
Please help me!
Thank you in advance.

 

[theDBguy]

You must be using the function in a query to display the results, correct? If so, you should be able to include the sort order table and sort by the sorting column in your query.

 

[jdraw]

You may be overthinking, but we need more context in order to provide focused options.

 

[Pat Hartman]

Also, a silly question. A recordset is an array so why are you putting an array into another array? Whatever you are doing to the second array, you should be doing to the recordset itself.

 

[MajP]

the table below is not saved in the database. The VBA code calculates and brings values for the member_credits column for each member ID.

I do not know what your database stores, but I would assume that can be calculated in query or at least make a temp table. That looks like it would be a simple aggregate query. You will have to explain where the data resides.
Because if that data can be queried or make a temp table then the solution is trivial.

Join that table to member Sort order table and sort by member_Credits, SortID

 

[devtr]

Thanks everyone for replying. The sort_order table is used only when there are duplicate member_credits. It will apply to only those members.

 

[devtr]

I added an Access file with a temporary table and code. Please help.

 

[jdraw]

devtr,

How about describing what you are trying to accomplish in business terms. Give us the bigger picture showing where/why this "sort issue" fits. Please provide context.

 

[MajP]

This is is overly complicated Rude Goldberg machine to do what appears to be a very trivial sort. As @jdraw said, please explain what you are trying to do so we can hopefully save you from going down this path. Your database table provided less insight.

 

[The_Doc_Man]

How do I sort the values in descending order?
Also, if two or more values are duplicates, the order depends on another table.

In common practice you can't actually do that. But you could build a query that somehow joins to the table that has the "tie-breaker" field and then have the query sort first on the main field and then the tie-breaker.

 

[Pat Hartman]

I'm with MajP, there is something wrong with your schema.

 

[devtr]

Appreciate you, brilliant guys! Thank you for your time. Your comments helped me. I am going to create a table to store these values and use query.
Thank you again for your time.

 

[Pat Hartman]

If you are having to hard code certain member_id's in this table, there is something wrong with your schema and it would be better to fix the schema than to add bandaids everytime the schema doesn't quite work.

 

[jdraw]

"I am going to create a table to store these values and use query."

That is probably the correct strategy, but it would help readers -and you -if you would describe the issue in none database terms.