Allocating points to a sorted list of records | Newbie | | Join Date: Feb 2007
Posts: 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!
|  | Expert | | Join Date: Jan 2007 Location: California
Posts: 3,835
| | | re: Allocating points to a sorted list of records
You need code to do this, do you want to proceed?
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,216
| | | re: Allocating points to a sorted list of records
Quote:
Originally Posted by 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! 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.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,216
| | | re: Allocating points to a sorted list of records
Quote:
Originally Posted by Rabbit 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.
| | Expert | | Join Date: Jan 2007 Location: California
Posts: 3,835
| | | re: Allocating points to a sorted list of records
Not at all a problem. The more solutions, the better.
| | Newbie | | Join Date: Feb 2007
Posts: 5
| | | re: Allocating points to a sorted list of records
Yes, please. I'm not an expert with code but I can manage it.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,216
| | | re: Allocating points to a sorted list of records
Quote:
Originally Posted by ECUweb 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. | | Expert | | Join Date: Jan 2007 Location: California
Posts: 3,835
| | | re: Allocating points to a sorted list of records
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.
| | Newbie | | Join Date: Feb 2007
Posts: 5
| | | re: Allocating points to a sorted list of records
Thank you. I'll try to make it work and come back with the outcome!
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,216
| | | re: Allocating points to a sorted list of records
Quote:
Originally Posted by 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. Thanks Rabbit! After all that work, how can I miss the obviouos.
|  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,449 network members.
|
