Randomly Assignment

[AccessIM]

I am trying to create a database whose main purpose will be to randomly
assign selection employees to a list of areas in the waehouse (10 Aisle, 6
Aisle, 5 Aisle, 4 Aisle, 3 Aisle and Cooler). There is a max number of
employees in each area (4, 3, 1, 4, 4 and 1 respectively) and a group of
selectors to choose from that may change everyday.

Currently, I have a form that 1) uses option boxes to assign the number of
selectors the supervisor wants to assign to each area and 2) uses a subform
with check boxes for the supervisor to choose (by checking the check box)
which employees are selecting that day.

This seems correct to me so far but I am stuck now that I am trying to
assign the selection employees to their areas. For example, of the 14
employees who are selecting on 8/16/08, 4 will be assigned to the 10 aisle, 3
will be assigned to the 6 Aisle, 1 will be assigned to the 5 Aisle, 3 will be
assigned to the 4 Aisle, 2 will be assigned to the 3 Aisle and 1 will be
assigned to the cooler.

I am able to select this criteria on my form that I have created but that's
as far as I have been able to go.

I am guessing this can be done with VBA code but I am a beginner at best
when it comes to writing code so if your response includes code please be
gentle.

If anyone has any suggestions, I would greatly appreciate it as this is
making me crazy! )

 

[Graham Mandeno]

Hi AccessIM

You can retrieve records in a random order if you use the Rnd function in
the ORDER BY clause. The trick is to pass a field containing a *positive*
numeric value as an argument to Rnd, otherwise it will be called only once
at the start of your query's execution and will therefore have the same
number in every row.

A good candidate for this would be a sequential autonumber field, or a
required date field, or the ASCII value of the first character in a required
text field.

For example:
Select * from Employees order by Rnd( [EmployeeID] )
or
... order by Rnd( [EmpStartDate] )
or
... order by Rnd( Asc( [LastName] ) )

Now that you are retrieving them in a random order, you can do what you like
with them - assign the first 4 to 10 Aisle", the next 3 to 6 Aisle, or
whatever.

 

[AccessIM]

Graham-

That worked perfectly and I actually understood it! ) Thank you so much!

Hi AccessIM

You can retrieve records in a random order if you use the Rnd function in
the ORDER BY clause. The trick is to pass a field containing a *positive*
numeric value as an argument to Rnd, otherwise it will be called only once
at the start of your query's execution and will therefore have the same
number in every row.

A good candidate for this would be a sequential autonumber field, or a
required date field, or the ASCII value of the first character in a required
text field.

For example:
Select * from Employees order by Rnd( [EmployeeID] )
or
... order by Rnd( [EmpStartDate] )
or
... order by Rnd( Asc( [LastName] ) )

Now that you are retrieving them in a random order, you can do what you like
with them - assign the first 4 to 10 Aisle", the next 3 to 6 Aisle, or
whatever.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am trying to create a database whose main purpose will be to randomly
assign selection employees to a list of areas in the waehouse (10 Aisle, 6
Aisle, 5 Aisle, 4 Aisle, 3 Aisle and Cooler). There is a max number of
employees in each area (4, 3, 1, 4, 4 and 1 respectively) and a group of
selectors to choose from that may change everyday.

Currently, I have a form that 1) uses option boxes to assign the number of
selectors the supervisor wants to assign to each area and 2) uses a
subform
with check boxes for the supervisor to choose (by checking the check box)
which employees are selecting that day.

This seems correct to me so far but I am stuck now that I am trying to
assign the selection employees to their areas. For example, of the 14
employees who are selecting on 8/16/08, 4 will be assigned to the 10
aisle, 3
will be assigned to the 6 Aisle, 1 will be assigned to the 5 Aisle, 3 will
be
assigned to the 4 Aisle, 2 will be assigned to the 3 Aisle and 1 will be
assigned to the cooler.

I am able to select this criteria on my form that I have created but
that's
as far as I have been able to go.

I am guessing this can be done with VBA code but I am a beginner at best
when it comes to writing code so if your response includes code please be
gentle.

If anyone has any suggestions, I would greatly appreciate it as this is
making me crazy! )