Allocating points to a sorted list of records

[ECUweb]

Hi,
I need to sort out a list of records based on the field "Weight" and then allocate points (from 1 to 10) to each record (in another field "Points") depending on the position of the record in the sorted list. Sorting out the list is not the problem for me but allocating the points is. The other problem is that when for example two records have the same weight, the allocated points should be the same, even when in the sorted list one of the records will be first and the other second. For example, I'v got the following "weights"(already sorted out)
65, 64, 50, 50, 45, 44, 42, 35, 22, 22, 20, 15, 14, 12, etc.
The allocated points for each record would be
10, 9, 8, 8, 7, 6, 5, 4, 3, 3, 2, 1
The last point (1) is allocated to the record with weight=15. I don't care what happens with the rest of the records, I only need to allocate points from 10 to 1.
I hope the explanation was clear enough.
Thank you for your help!

[Rabbit]

You need code to do this, do you want to proceed?

[ADezii]

Hi,
I need to sort out a list of records based on the field "Weight" and then allocate points (from 1 to 10) to each record (in another field "Points") depending on the position of the record in the sorted list. Sorting out the list is not the problem for me but allocating the points is. The other problem is that when for example two records have the same weight, the allocated points should be the same, even when in the sorted list one of the records will be first and the other second. For example, I'v got the following "weights"(already sorted out)
65, 64, 50, 50, 45, 44, 42, 35, 22, 22, 20, 15, 14, 12, etc.
The allocated points for each record would be
10, 9, 8, 8, 7, 6, 5, 4, 3, 3, 2, 1
The last point (1) is allocated to the record with weight=15. I don't care what happens with the rest of the records, I only need to allocate points from 10 to 1.
I hope the explanation was clear enough.
Thank you for your help!

I do have a solution but it is a little more complicated than I would like it to be. Rabbit will probably have a better solution but if you still are interested, please let me know.

[ADezii]

You need code to do this, do you want to proceed?

Rabbit:
Not trying to jump in, just thought that it was an interesting problem.

[Rabbit]

Not at all a problem. The more solutions, the better.

[ECUweb]

Yes, please. I'm not an expert with code but I can manage it.

[ADezii]

Yes, please. I'm not an expert with code but I can manage it.

1. Enter all your Weight values into a Table called tblWeights with a [Weight] Field in any order.
2. Create a Query called qryUniqueTopTenWeights with the following specs:

1 Field only ==> [Weight] DESC

Top Values = 10

Unique Values = Yes

(This Query will output the 10, largest, 'unique' Weight values)
Declare 2 Public Arrays:

Public arrWeights(1 To 10) As Long      'will hold the 10 highest Weight values
Public arrPoints(1 To 10) As Long                    'will hold the Point values from 1 to 10 ASC order from 1 to 10

Initialize the Arrays before running the final Query (Critical). They are Public and will retain their values. 1 Array (arrWeights) will contain the 10 highest weights from qryUniqueTopTenWeights, and the other (arrPoints) will contain the Point values 1 (Highest) to 10 (Lowest):

Dim MyDB As DAO.Database, MyRS As DAO.Recordset
Dim intCounter As Integer
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("qryUniqueTopTenWeights", dbOpenDynaset)
 
Do While Not MyRS.EOF
  intCounter = intCounter + 1
  arrWeights(intCounter) = MyRS![Weight]
  arrPoints(intCounter) = intCounter
    MyRS.MoveNext
Loop

Create a Query called qryAssignedPoints with 1 Field [Weight] DESC and a Calculated Field called Points which will look like this: Points: fAssignPoints([Weight])

Copy the Public Function fAssignPoints to a Standard Module:

Public Function fAssignPoints(intWeight As Long) As Long
On Error GoTo Err_fAssignPoints
Dim intCounter As Integer
 
For intCounter = LBound(arrWeights) To UBound(arrWeights)
  If arrWeights(intCounter) = intWeight Then
    fAssignPoints = arrPoints(intCounter)
      Exit Function
  End If
Next
 
Exit_fAssignPoints:
  Exit Function
 
Err_fAssignPoints:
  MsgBox Err.Description, vbExclamation, "Error in fAssignPoints()"
  Resume Exit_fAssignPoints
End Function

Run qryAssignPoints - typical Output would be:

Weight    Points
65      1
64      2
50      3
50      3
45      4
44      5
42      6
35      7
22      8
22      8
20      9
15    10
14      0
12      0

If you need any further explanation or assistance, I'll be monitoring this Post. Like I stated previously, there may be a simpler solution, but if there is one - it escaped me.

[Rabbit]

This is along the lines of what I was going to suggest. Except the OP wanted the points in the reverse order so he'll have to reverse that array.

[ECUweb]

Thank you. I'll try to make it work and come back with the outcome!

[ADezii]

This is along the lines of what I was going to suggest. Except the OP wanted the points in the reverse order so he'll have to reverse that array.

Thanks Rabbit! After all that work, how can I miss the obviouos.