We have 3 machines (Line 1,Line 2,Line 3) available where only 2 at any given time operate over our 5 days work week.I have 2 workers for day shift and 2 workers for night shift that I need to automatically allocate their name against the machines that are rostered to be run. One worker per machine. The 4 workers have a set roster where they work every day. If Line 1 is not running then the 2 day shift workers need to be rostered on Lines 2 and 3. Same for night shift. If Line 2 is not scheduled to run then the 2 day shift workers need to be automatically allocated to Lines 1 and 3. The other variable is that if a worker was rostered on a certain machine the previous day then the following day they would be rostered onto a different machine.

Anyone have any suggestions on how I can do this using 2 tables. Table 1 showing a list of dates that machines are rostered to run and a separate table showing the normal work hours for the workers.

Any help appreciated.