You're going to need a field in Tasks that's yes/no. Also you need a field in Employee's that's task ID (Perhaps multiple fields).
Code a loop that selects the first employee record...then makes a recordset of all available tasks of the appropriate type that are unassigned. Grab the first record from that recordset, and set it's assigned flag to yes, and set the Employee's task field to Task ID. Take the next employee and repeat the loop until out of tasks or employees.
If you make a second or third task field then you can loop again and fill those in.
You can then make your relationships one to one based on task ID.
Just my thoughts...let me know if you need any coding help.
Thanks, that sounds like a good suggestion, however I'm finding that I will have to have these tasks be able to be assigned and tracked over a long period of time.
I was thinking that I could use a crosstab query to populate a field in the employee table with a total of assigned tasks and then select the record with the least number in that total column (filtered by type of task to be done) and assign that one.
The problem I run into is that since I'm going for equilibrium ideally if everything works I run into all employees having the same number of tasks assigned so that doesn't work.
I also want to cap the number of tasks assigned to an employee that were created on the same day. For instance if there are 80 tasks from the 1st and I add a new employee he or she would not get ALL 80 tasks (with a 48hr deadline) just by virtue of the fact that they had none in que. The daily cap would be an arbitrary number assigned by me for each employee. (Once a task is assigned it creates a new record in a third table to keep track of them all and that is where the Count would come from.)
Now, I have created something like this that works for one Task at a time but my problem is limited prog. expierience and the fact that I see no way to loop something like this without the logic breaking down.
I thought about basing the rotation on the autonumber in access to just set a random rotation order and do an update that goes something like...
Autonumber + Count of Employees = Bottom of selection list, however this ignores the total of active volume that each employee would have.
So you see where I'm going with this I hope (cause the more I think on it the less I do)....