Random Assignment

[Guest]

I have a database that includes an Observers table (32 records) and an
Associates table (357 records). We randomly assign each observer a group of
associates and are currently doing this through a very manual process in
Excel.

I would like to know if there is a way to randomly assign each observer
their team using Access. I have read through the other postings and see ways
to do a random selection of records but I can't see how to assign them to an
observer.

Any suggestions would be greatly appreciated.

 

[George Nicholson]

One approach:

Create a query listing all associates. Add a calulated field that generates
a random number between 1 and 32:
RandomAssignment: Int(32*Rnd([AssocID])+1)

(This assumes AssocID is a field in the Associates table and that it is a
unique number >0. Needed as shown to force a new random number to generate
for each record in the query rather than one number for all records. It has
no effect on the results of the calculation, other than whether it occurs).

Note that each time you run this query a different set of results will be
generated, so to "save" results this would need to be a make-table query or
an update to the Associates table, (and you need to decide this before you
see any output).

Also note that random results and "pretty" results can be very different
animals. 357/32 = 11.156, but making strictly random assignments you could
easily end up with some observers having 20 associates assigned and some
with only 5. Its highly unlikely each observer will have a nice clean set of
11 or 12 assignments. Your choices would include: accept the results (warts
and all), keep rerunning until you get a distribution that's 100% perfect,
or get something reasonably close to pretty and then make some manual
(un-random) modifications. I suspect the latter approach may be the
same "very manual process" you go through in Excel.

Obviously, if your Observers don't have ID's 1-32 you'll need to create some
sort of temporary cross reference mapping for translation.

Another approach (and probably better if you really want to maintain as
close to 100% randomness as possible but an even number of assignments for
each observer) would be via VBA code and writing assignments to a table:
fill an array with 357 associates. For each observer, pull 11 random
associates from the array (removing them from the array at the same time so
the array keeps shrinking). For the "leftover" 5 associates, you could
assign a 12th associate to a random 5, 1st 5 or last 5 observers.

HTH,

 

[Guest]

Thank you for your time, George. Since reading your reply, I have been
trying to find some instruction as to how to do an array because I am very
new to writing code in Visual Basic but I am getting more confused with
everything I read.

An array sounds like it would be best for what I am trying to do. I would
also need it to remove the names from the array each time so that it doesn't
choose the same associate for two different observers.

Could you please elaborate on how to create the array you mentioned in your
reply? Thank you again.

One approach:

Create a query listing all associates. Add a calulated field that generates
a random number between 1 and 32:
RandomAssignment: Int(32*Rnd([AssocID])+1)

(This assumes AssocID is a field in the Associates table and that it is a
unique number >0. Needed as shown to force a new random number to generate
for each record in the query rather than one number for all records. It has
no effect on the results of the calculation, other than whether it occurs).

Note that each time you run this query a different set of results will be
generated, so to "save" results this would need to be a make-table query or
an update to the Associates table, (and you need to decide this before you
see any output).

Also note that random results and "pretty" results can be very different
animals. 357/32 = 11.156, but making strictly random assignments you could
easily end up with some observers having 20 associates assigned and some
with only 5. Its highly unlikely each observer will have a nice clean set of
11 or 12 assignments. Your choices would include: accept the results (warts
and all), keep rerunning until you get a distribution that's 100% perfect,
or get something reasonably close to pretty and then make some manual
(un-random) modifications. I suspect the latter approach may be the
same "very manual process" you go through in Excel.

Obviously, if your Observers don't have ID's 1-32 you'll need to create some
sort of temporary cross reference mapping for translation.

Another approach (and probably better if you really want to maintain as
close to 100% randomness as possible but an even number of assignments for
each observer) would be via VBA code and writing assignments to a table:
fill an array with 357 associates. For each observer, pull 11 random
associates from the array (removing them from the array at the same time so
the array keeps shrinking). For the "leftover" 5 associates, you could
assign a 12th associate to a random 5, 1st 5 or last 5 observers.

HTH,

I have a database that includes an Observers table (32 records) and an
Associates table (357 records). We randomly assign each observer a group
of
associates and are currently doing this through a very manual process in
Excel.

I would like to know if there is a way to randomly assign each observer
their team using Access. I have read through the other postings and see
ways
to do a random selection of records but I can't see how to assign them to
an
observer.

Any suggestions would be greatly appreciated.