Autopopulating Table Field from Another Table

[Ganymede]

Here is what I am trying to accomplish. I have two tables. The first (Table 1) contains a list of tasks that need to be completed, including a field for the person the task is assigned to ("Assigned Employee"). The second table (Table 2) contains a list of 5 employees who can be assigned to each task (i.e., the names of indiviudals who will populate the "Assigned Employee" field in the first table).



I would like the "Assigned Employee" field in Table 1 to automatically populate with an employee from Table 2 each time a new record is created. Ideally, whatever program I use would cycle through each employee before reusign any of them to ensure assignments are distributed equally.

Is there any way to accomplish this in access?

[CJ_London]

perhaps have a query along the lines of

SELECT employeePK, count(employeeFK) as Ctr
FROM table2 lEFT JOIN table1 ON table2.employeePK=table1.employeeFK
GROUP BY employeePK
ORDER BY count(employeeFK)

Then a second query based on that one

SELECT TOP 1 employeePK FROM qry1

Finally in your form something like

employeeFK=dlookup("employeePK","Qry2")


The first query is ordering with the least assigned at the top, the left join ensures all employees are available. When starting that is all of them so the first one is effectively chosen randomly in the second query. Once assigned, the next time you add a new task, that employee is at the bottom, so TOP 1 will choose a different employee. This continues until they have all been assigned once. At which point the cycle starts again - but wont necessarily select TOP 1 in the same order.

[jojowhite]

you can also add code to the BeforeInsert Event of the Form.
Open any form and add some entries. See the code of the BeforeInsert event.

[Ganymede]

Thank you both! Marking as solved.