435,222 Members | 1,730 Online
Need help? Post your question and get tips & solutions from a community of 435,222 IT Pros & Developers. It's quick & easy.

# Allocating points to a sorted list of records

 P: 5 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! Feb 27 '07 #1
9 Replies

 Expert Mod 10K+ P: 12,366 You need code to do this, do you want to proceed? Feb 27 '07 #2

 Expert 5K+ P: 8,638 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. Feb 27 '07 #3

 Expert 5K+ P: 8,638 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. Feb 27 '07 #4

 Expert Mod 10K+ P: 12,366 Not at all a problem. The more solutions, the better. Feb 27 '07 #5

 P: 5 Yes, please. I'm not an expert with code but I can manage it. Feb 28 '07 #6

 Expert 5K+ P: 8,638 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] DESCTop Values = 10Unique Values = Yes(This Query will output the 10, largest, 'unique' Weight values) Declare 2 Public Arrays: Expand|Select|Wrap|Line Numbers 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): Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers 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. Feb 28 '07 #7

 Expert Mod 10K+ P: 12,366 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. Feb 28 '07 #8

 P: 5 Thank you. I'll try to make it work and come back with the outcome! Feb 28 '07 #9

 Expert 5K+ P: 8,638 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. Feb 28 '07 #10