Assign accounts to associates randomally

[wilkes77]

I have two groups of associates, unit a which has 7 associates and unit b which has 8 associates, who work accounts daily based on unit type. Each day I have to randomly assign 700+ accounts evenly to the associates based on unit. Using Excel I add a random column to randomly number the accounts, then sort by unit type and random number, paste the 7 or 8 names and drag down to assign to associates.


I need to be able to do this in Access as the process has various steps before this and takes about 30 minutes each day to complete.

Also, I need the associate list to be dynamic; I setup a yes/no box in the associate table to uncheck if associates are absent on a given day. I assume I need to do some type of loop statement in VBA code to loop through active associates and assign to accounts but my VBA skills are very week.

I have an Associate table, as below, which I had to assign the accounts evenly based on Unit. Any help would be grateful as I am completely lost on this.

Unit	Associate	Available
A	John Smith	Yes
A	James Doe	No
B	Sandy Jones	Yes
B	Camille Madison	Yes
B	Doris Adams	Yes

I created a random assignment query but the number of employees is static and we occasionally need to add and remove associates and account for absences.

Any help would be great!

[ranman256]

divider.mdb

Look at this.

[wilkes77]

This seems to work however when I try to put the counts into a query I cannot get this to work. Currently I have a table with all unassigned accounts and a query to show associate and unassigned accounts totals as the database is more involved and will have 4 different type of accounts and 4 different groups of associates. How can I change the code to look at a query v/s the totals in a form? I have tried various times but keep getting an error on the SQL statement line .


I have this query:

Category	#AvailAssociates	#Accts	AcctsperAssociate
Unit A	2	300	150
Unit B	3	675	225

Associates Table:

Unit	Associate	Available
A	John Smith	Yes
A	James Doe	Yes
B	Sandy Jones	Yes
B	Camille Madison	Yes
B	Doris Adams	Yes

And have a table with all Unassigned accounts. I need to assign associates to accounts by unit.

[ranman256]

You will need to build query counts / AcctType
Then to assign join on Acct type and let the form text boxes:
cbo to pick an AcctType (AT)
#emps/AT
#accts/AT
run macro