Random records query (1 Viewer)

chrismc10

New member
Local time
Today, 10:02
Joined
Jun 21, 2021
Messages
2
Good day
I created a system to randomly select jury members from several assigned military units (This is a project I am working for a branch of the military.) What I am currently doing is having the database create a random number using the RND function, then I am having a query pull the top 4 using records based on the random number. The problem is that the list may have records from the same unit twice. For example: 1st Lt XXX and 1st Lt YYY may be pulled, randomly, but they are both from the same unit. Is there a clean way to make sure that the top X are pulled but doing so while keeping the units distinct? The relevant fields at play here are: FullName, Rank, Unit, Sort (which is the random number). I thought about creating a separate query that builds a new random sort number by unit and then applies that number to each record, which works until I get to where I need the random records - If I pull the top 4, for example, and if the unit that has the lowest sort number has lets say 2 person in that rank, it will give me both of those individuals. I hope this makes sense. In the end what I am trying to do is get a random set of 1st Lt where the unit is totally distinct with no repeats.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:02
Joined
May 21, 2018
Messages
8,463
Random draws can have repetition. So if you pull three random integers from 1 to 10, there is nothing to prevent you from returning 1,1,1 or 2,2,2. You would likely have to write some code to pull numbers without replacement. Random does not mean fair, and often people want a fair assignment. The common one is assigning duties vs urinalysis. In assigning weekend duties you may want to draw randomly, but exclude from the draw those people already assigned this month/year. In urinalysis you do not care (in fact it is desired) that someone assigned last time can get assigned again.

So you would likely Loop the units. Then pull the 1st Lts from a random query for that unit, check if they are not already on the assigned list. If not write to the assigned Temp Table. If a unit does not have any available 1st Lts not already assigned then move to the next unit. Keep looping until you fill your quota. I have written ton of threads on this subject, may want to look at some of the similar threads below.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:02
Joined
May 21, 2018
Messages
8,463
Here is an example I did that had the most complicated and ever changing set of rules.

The level of complexity in writing the code depends on some details.
Lets assume you have 10 units to draw from and you never have more than 10 names and all units will always have at least 1 1st Lt available then this is relatively easy. But gets more complicated if say you have to draw 22 1st Lts and some units may or may not have one available. Or in this case less than 2 available. If you have to prepare for the worst case the logic is to pass to the procedure the number to draw. Then get a sorted random list of units. Start looping those and return the unit. Pull a random Lt from a new query from that unit and not in the already selected list. If none available in that unit move to next unit. Write the 1st Lt to the selected table. Keep looping the units (in a continuous circular loop) until you fill number required.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:02
Joined
Feb 19, 2002
Messages
42,970
If you want to pull one person each from four different units, you need to run the query four times. Once for each unit. The rnd selection is over the entire set of data, not a subset of data. That is why you need to do this four times.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:02
Joined
Feb 28, 2001
Messages
26,999
As Pat and MajP have noted, "Random" is inherently fair BUT might seem biased. Your idea of assigning a random number would work fairly for an isolated draw - but perhaps not for multiple draws. Do you have rules regarding how often a person can get chosen? Because "random" doesn't know about previous service. If you tracked previous service and added (or in this case, subtracted) something as a credit for recent service, then your "assign numbers and pick the top X" is perfectly reasonable.

Let's consider the nightmare of FAIRLY assigning random jurors from a pool. You would have to assign a juror and then re-weight every unpicked juror against the demographics of that first selected juror. So you would have to have weights for gender, race or ethnicity, religion, occupation, and "alternate ability" status (handicapped, autistic spectrum, etc.) So I don't know what your constraints might need to be, but the method would have to include biases introduced by previous selections.

Stated another way, random picks in a vacuum are always fair. Random picks in a minefield of constraints are rarely fair to the person who was picked.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:02
Joined
May 7, 2009
Messages
19,169
sample of random record.
 

Attachments

  • random_jury.accdb
    572 KB · Views: 396

chrismc10

New member
Local time
Today, 10:02
Joined
Jun 21, 2021
Messages
2
Thank you all. After going through the replies and looking at my options vs. the desires of the folks in charge, what I did was created a query for each rank that pulls 1 random member and places him/her into an export table (will be used to generate the xls file). Then I have a delete query that deletes that unit from the temp table. On the form I have an unbound text field for each rank that allows the user to indicate how many members they want per rank. In the vba, I have the unbound text field as a parameter to define how many times the random select query followed by the delete query will be ran per rank. This may not be the cleanest or most efficient way of handling it, but its working well and is pretty fast. In all my tests I have been given "random" members in each rank with the panel being completely distinct with respect to the units. So if we are pulling 20 members, we have 1 member from 20 different units.
 

Users who are viewing this thread

Top Bottom