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"(alrea dy 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!
9 2203 Rabbit 12,516
Recognized Expert Moderator MVP
You need code to do this, do you want to proceed?
ADezii 8,834
Recognized Expert Expert
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"(alrea dy 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 8,834
Recognized Expert Expert
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 12,516
Recognized Expert Moderator MVP
Not at all a problem. The more solutions, the better.
Yes, please. I'm not an expert with code but I can manage it.
ADezii 8,834
Recognized Expert Expert
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 qryUniqueTopTen Weights 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 qryUniqueTopTen Weights, 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 qryAssignedPoin ts 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 12,516
Recognized Expert Moderator MVP
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.
Thank you. I'll try to make it work and come back with the outcome!
ADezii 8,834
Recognized Expert Expert
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Der Andere |
last post by:
I need to implement a sorted (ordered) list. STL has no sorted list type as
far as I know. Is there a (straight) way to implement a sorted list using
STL?
BTW: The type of items in the list will be a class. Is it necessary to
implement the > or < operators or to write a compare-function that returns
the larger or smaller of two classes?...
|
by: binary-nomad |
last post by:
Hello,
To followup my last post, how do I keep a field in a table already
sorted. eg. if a field in it going to have values like "10", "3330" and
"1", I want the row with the "1' first in the table, and the "10"
second and so on. I want this to happen when I UPDATE, *not* when
SELECT-ing, as, according to the last post, if I 'm doing a SELECT...
|
by: boss_bhat |
last post by:
Hi all ,
I am beginner to C programming. I have a defined astructure like the
following, and i am using aliases for the different data types in the
structure,
typedef struct _NAME_INFO {
struct _NAME_INFO *Next;
ULONG LastId;
ULONG Id;
PVOID Value;
|
by: J L |
last post by:
I want to create a sorted list whose values are themselves sorted
lists. I wrote the following simple test program but it does not
behave as I would expect.
What I wanted to do was have the doorConflictList be keyed on a door
ID and contain a sorted list called conFlictList. I wrote this
expecting the following
doorConflictList should...
|
by: shrishjain |
last post by:
Hi All,
I need a type where I can store my items in sorted order. And I want to
keep adding items to it, and want it to remain sorted. Is there any
type in .net which I can make use of.
I see there is SortedList<key, value> for hash tables, but could find
anything for a sorted list.
Currently I am using List<string> and whenever I add...
| |
by: acosgaya |
last post by:
Hi, I would like some help as how to approach the following problem:
I have a set of d-dimensional points (e.g (3,5,8,9,5) is a 5-dimensional point), and I need to sort the points on each of the coordinates.
The algorithm that I am trying to implement says that the resulting data structure is a multi-pointer list ((d-1)tuply threaded list),...
|
by: shortyes |
last post by:
Is there any easy way to sort a list of Points by either its X or Y or
both?
Tried
sortedlist (of String, Point) where string is Point.X & "," & Point.Y
as the key
sortedlist (of Point, Point) using the Point as the key
I could always use try and true method of a binary search algorithm
but I rather not.
|
by: Hunk |
last post by:
Would like some advice on the fillowing
I have a sorted list of items on which i require to search and
retrieve the said item and also modify an item based on its identity.
I think an Map stl container would be most suited. Am i correct in my
assumptions
Problem
Reocrds are already stored with a unique identity in a binary
format.The...
|
by: Hunk |
last post by:
Hi
I have a binary file which contains records sorted by Identifiers
which are strings. The Identifiers are stored in ascending order. I
would have to write a routine to give the record given the Identifier.
The logical way would be to read the record once and put it in an STL
container such as vector and then use lower_bound to search for...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |