Randomize records into different groups

[Mo]

Hello,

I need to randomise participants in an Access 2000 db into four groups.
Let's say these are 1, 2, 3 and 4. This in itself is not the problem as
it's pretty straight forward.

The problem is that I need to keep the numbers of participants in each
group approximately equal, say to within a tolerance of 5.

Can anyone point me in the right direction please?

 

[Keith]

Hello Mo,

That's a good question. As I think of it, it may depend on how many are in
your uiverse when I have selected random records in Access, I build a table
with an index and random numbers. The random numbers table has as many
records as the data table, and I make sure there are a lot of digits to the
right of the decimal in the random number. I link my random number table
with the data table using a query, and then select four groups based on the
values in the random numbers. By changing the classification of random
numbers you should be able to get close.

Another approach would be to use VBA to make the assignments of random
numbers into groups 1,2,3, and 4 so that they are always even.

Hope this helps.

Keith

 

[BeWyched]

Hi Mo

I think you have a problem here - random is random, so, restricting group
membership numbers within a tolerance will, by definition, remove the
randomness.

The only way I can think to do this is to randomly index the members and
sort into an order before allocating to a group. Then simply take the first
1/4 in group 1 etc. As the index was created randomly, the end result will be
random with exact numbers (or within 1 member at worst) in each group.

By the way have you read the Help files re Rnd and Randomize as Access'
definition of 'random' isn't quite what it seems!

Cheers.

BW

 

[Mo]

Hello Mo,

That's a good question. As I think of it, it may depend on how many are in
your uiverse when I have selected random records in Access, I build a table
with an index and random numbers. The random numbers table has as many
records as the data table, and I make sure there are a lot of digits to the
right of the decimal in the random number. I link my random number table
with the data table using a query, and then select four groups based on the
values in the random numbers. By changing the classification of random
numbers you should be able to get close.

Another approach would be to use VBA to make the assignments of random
numbers into groups 1,2,3, and 4 so that they are always even.

Hope this helps.

Thanks very much for the response Keith. I need to do it using VBA but
don't have much of an idea how to go about keeping the random
assignments to the groups so that they come out more or less equal.

 

[John W. Vinson]

Thanks very much for the response Keith. I need to do it using VBA but
don't have much of an idea how to go about keeping the random
assignments to the groups so that they come out more or less equal.

Try something like this from my "boilerplate" samples:

You can use the Top Values property of a query, with help
from a little VBA. Put this little function into a Module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])

in a vacant Field cell, where [fieldname] is any field in
your table - this forces Access to give a different random
number for each record.

Sort the query by Shuffle, and set its Top Values property
to the number of records you want to see.

You can use five different queries using TOP 20%, TOP 40%, TOP 60% and so on,
and exclude values already populated by using the Unmatched wizard to accept
only values not already populated.

John W. Vinson [MVP]