ROUND ROBIN: How to assign values from one table to another

[mbannen]

I have two tables:

Table one: Tasks
Table two: Employees

Within the Tasks table is an Assigned To field.

Imagine there are 100 tasks and 10 employees.
I want to file the ASsigned To field in the Tasks table with names from the Employees table using a round robin approach. I don't know VB.

Please help! URgent!
mbannen@topminds.com

 

[dhookom]

You don't know VB and we don't know what you mean by "round robin approach" (and my college degree was Parks and Recreation). Why don't you take a few minutes and type about 3 employee records and 5 tasks and show us what kind of results you expect. I think you might want a cartesian query but who knows?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[JonFer]

Can multiple people be assigned to a task? Do you need a history of who was assigned to a task if there will be some turnover?

 

[mbannen]

Thanks for the notes. Here's a definition of round robin, answers to the above questions and an example of how the records should look:

ROUND ROBIN: A round robin is a logical arrangement of "turn taking" within a computer environment. In a web server setting, for example, two servers would "take turns" accessing user information.

In the context of my question is just means to start at the beginning of the employee list, assign them to tasks, and when I reach the last employee, start over with the first again.

ANSWERS:
- One and only one person can be assigned to a task
- Do not need a history.

RECORD EXAMPLES:
Employee table:
Employee ID Employee Name
1 Bill Smith
2 Bob Newhart
3 Oprah

Tasks table (start state)

Task_ID Assignee_Nm
0000001
0000002
0000005
0000007
0000020
0000027
0000031
0000040

Tasks table (end state)

Task_ID Assignee_Nm
0000001 Bill Smith
0000002 Bob Newhart
0000005 Oprah
0000007 Bill Smith
0000020 Bob Newhart
0000027 Oprah
0000031 Bill Smith
0000040 Bob Newhart

 

[OhioSteve]

To perform that task repeatedly, you will need a loop. Consequently, you will need a structured programming language.

However, I think that you could do the assignments once using the modulus operator.

 

[mbannen]

I have limited VBA skills.
How would I do this? I couldnt' find any documentation on the modulus operator.

 

[dhookom]

Are your tasks always assigned in the order of the Task_ID?

If you don't know VBA then you will probably need to rely on code created for you. I trust you have provided the required field and table names. Also, I would recommend placing the employee ID in the tasks table rather than their name.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[mbannen]

dhookom:

I'm going agains the backend of another application, using Access to both select records (from the Tasks table) I want to update, and then to Update them from a local employees table. The Task IDs are always assigned in order, but I may be getting Tasks 1, 7, 1005, because the selection is based on a set of criteria.

Re: employee name. I agree. I think they did this to avoid having to do another lookup to be able to display the user name through the GUI.

Any ideas on how to solve this problem?

 

[dhookom]

mbannen,
You need to create some pseudo code. This outlines the steps you would use to solve this. For instance:
1) make a list of tasks that need to be done in the order they should be assigned (Task List)
2) make a list of available employees in the order they should be assigned to tasks (Employee List)
3) start with the first task and assign the first employee on the list to the task
4) ...
5) ...
6) loop back to step X and continue until there are no more tasks requiring assignment

If you can write the above, you have a good beginning to writing VBA. I would add all the steps as comments in my code and then write the actual code between the comments.

If you reply with the steps, someone should be able to assist with writing the code between the steps.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[willir]

Round robins are tough.

For starters, you can include a field "Selected" (boolean yes/no), and you can use

For each employee
'Assign a task
Select top 1 task from tblTask Selected = False
'Then flag task as assigned
Next
Repeat loop again if tasks are not all assigned

If you need to balance "time" or difficulty, then you can use an OrderBy clause when selecting the clause.

I had thought of using Select ... Not In but since multiple people are involved, I would suspect it would get messy.

 

[JonFer]

This assigns employees to a task but doesn't update your Tasks table:

Select TaskID, EmployeeID, EmployeeName
From Tasks, Employees
Where ((Select Count(*) from Tasks as a 
       Where a.Task_ID < Tasks.Task_ID And a.Assignee_Nm Is Null) Mod 
       (Select Count(*) from Employees))=(Select Count(*) from Employee as a where a.EmployeeID<Employee.EmployeeID)
And Tasks.Assignee_Nm Is Null

You can then join this to your Tasks table on TaskID and update the Assignee field:

UPDATE Tasks INNER JOIN QueryName ON Tasks.Task_ID = QueryName.Task_ID 
SET Tasks.Assignee_Nm = QueryName.EmployeeName

As Duane mentioned, using EmployeeID is preferred over the name.