By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,222 Members | 1,730 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
9 Replies


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

ADezii
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

ADezii
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

Rabbit
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

ADezii
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] DESC
Top Values = 10
Unique Values = Yes
(This Query will output the 10, largest, 'unique' Weight values)
Declare 2 Public Arrays:
Expand|Select|Wrap|Line Numbers
  1. Public arrWeights(1 To 10) As Long      'will hold the 10 highest Weight values
  2. 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
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  2. Dim intCounter As Integer
  3.  
  4. Set MyDB = CurrentDb()
  5. Set MyRS = MyDB.OpenRecordset("qryUniqueTopTenWeights", dbOpenDynaset)
  6.  
  7. Do While Not MyRS.EOF
  8.   intCounter = intCounter + 1
  9.   arrWeights(intCounter) = MyRS![Weight]
  10.   arrPoints(intCounter) = intCounter
  11.     MyRS.MoveNext
  12. 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
  1. Public Function fAssignPoints(intWeight As Long) As Long
  2. On Error GoTo Err_fAssignPoints
  3. Dim intCounter As Integer
  4.  
  5. For intCounter = LBound(arrWeights) To UBound(arrWeights)
  6.   If arrWeights(intCounter) = intWeight Then
  7.     fAssignPoints = arrPoints(intCounter)
  8.       Exit Function
  9.   End If
  10. Next
  11.  
  12. Exit_fAssignPoints:
  13.   Exit Function
  14.  
  15. Err_fAssignPoints:
  16.   MsgBox Err.Description, vbExclamation, "Error in fAssignPoints()"
  17.   Resume Exit_fAssignPoints
  18. End Function
Run qryAssignPoints - typical Output would be:
Expand|Select|Wrap|Line Numbers
  1. Weight    Points
  2. 65      1
  3. 64      2
  4. 50      3
  5. 50      3
  6. 45      4
  7. 44      5
  8. 42      6
  9. 35      7
  10. 22      8
  11. 22      8
  12. 20      9
  13. 15    10
  14. 14      0
  15. 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

Rabbit
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

ADezii
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

Post your reply

Sign in to post your reply or Sign up for a free account.