Schedule Rotation

[lynxsta911 via AccessMonster.com]

I have a table in which I'd like to depict a rotating shift schedule. I can
generate a complete rotation in Excel and then import into Access, but I'd
like to automate the process by using a query. How do I build a query to
append to my schedule table?

Rotation: 2 days, 2 nights, 4 off (D D N N 0 0 0 0)
Shifts: D = 7am-7pm, N = 7pm-7am
Staffing: 4 teams of 4 employees each (Team A, B, C, D) so there are 4 people
on dayshift and 4 people on nightshift every day

Schedule Table:
Oct 1 | D | Emp1 | TeamA
Oct 1 | D | Emp2 | TeamA
Oct 1 | D | Emp3 | TeamA
Oct 1 | D | Emp4 | TeamA
Oct 1 | N | Emp13 | TeamD
Oct 1 | N | Emp14 | TeamD
Oct 1 | N | Emp15 | TeamD
Oct 1 | N | Emp16 | TeamD

Oct 2 | D | Emp1 | TeamA
Oct 2 | D | Emp2 | TeamA
Oct 2 | D | Emp3 | TeamA
Oct 2 | D | Emp4 | TeamA
Oct 2 | N | Emp13 | TeamD
Oct 2 | N | Emp14 | TeamD
Oct 2 | N | Emp15 | TeamD
Oct 2 | N | Emp16 | TeamD

Oct 3 | D | Emp5 | TeamB
Oct 3 | D | Emp6 | TeamB
Oct 3 | D | Emp7 | TeamB
Oct 3 | D | Emp8 | TeamB
Oct 3 | N | Emp1 | TeamA
Oct 3 | N | Emp2 | TeamA
Oct 3 | N | Emp3 | TeamA
Oct 3 | N | Emp4 | TeamA

Oct 4 | D | Emp5 | TeamB
Oct 4 | D | Emp6 | TeamB
Oct 4 | D | Emp7 | TeamB
Oct 4 | D | Emp8 | TeamB
Oct 4 | N | Emp1 | TeamA
Oct 4 | N | Emp2 | TeamA
Oct 4 | N | Emp3 | TeamA
Oct 4 | N | Emp4 | TeamA

Oct 5 | D | Emp9 | TeamC
Oct 5 | D | Emp10 | TeamC
Oct 5 | D | Emp11 | TeamC
Oct 5 | D | Emp12 | TeamC
Oct 5 | N | Emp5 | TeamB
Oct 5 | N | Emp6 | TeamB
Oct 5 | N | Emp7 | TeamB
Oct 5 | N | Emp8 | TeamB

Oct 6 | D | Emp9 | TeamC
Oct 6 | D | Emp10 | TeamC
Oct 6 | D | Emp11 | TeamC
Oct 6 | D | Emp12 | TeamC
Oct 6 | N | Emp5 | TeamB
Oct 6 | N | Emp6 | TeamB
Oct 6 | N | Emp7 | TeamB
Oct 6 | N | Emp8 | TeamB

Oct 7 | D | Emp13 | TeamD
Oct 7 | D | Emp14 | TeamD
Oct 7 | D | Emp15 | TeamD
Oct 7 | D | Emp16 | TeamD
Oct 7 | N | Emp9 | TeamC
Oct 7 | N | Emp10 | TeamC
Oct 7 | N | Emp11 | TeamC
Oct 7 | N | Emp12 | TeamC

Oct 8 | D | Emp13 | TeamD
Oct 8 | D | Emp14 | TeamD
Oct 8 | D | Emp15 | TeamD
Oct 8 | D | Emp16 | TeamD
Oct 8 | N | Emp9 | TeamC
Oct 8 | N | Emp10 | TeamC
Oct 8 | N | Emp11 | TeamC
Oct 8 | N | Emp12 | TeamC

We have casual employees that fill in for holiday leave and sick days which
happen at random, so I would like to be able to plug in any name into any
slot at any time. However, I would like to start with the set rotation for
each crew for the year. Ideally, I would like to build a year's worth of the
schedule at a time, then be able to modify the employees in each slot as
needed.

 

[Michel Walsh]

Instead of calling the rotation { D, D, N, N, 0, 0, 0, 0} I would call them
{0, 1, 2, 3, 4, 5, 6,7}, kind of having a 'primary key', if you prefer:

Rotations ' table name
RotID, RotName ' fields
0 D
1 D
2 N
3 N
4 null
5 null
6 null
7 null 'data


Then, it seems it is enough to track each team (rather than each employee in
each team):


Day0 ' table name
Team OnRotation ' field name
A 0
D 2
B 6
C 5
.... ' data


so, to get the schedule at day + d, use:


SELECT team, rotName
FROM Day0 INNER JOIN Rotations
ON ((Day0.OnRotation + [d] ) MOD 8 ) = Rotations.RotID
WHERE NOT(rotName IS NULL)



(where [d] is a parameter, or from a driver table (a table having
integers from 0 to N ) )




Hoping it may help,
Vanderghast, Access MVP

 

[lynxsta911 via AccessMonster.com]

Thank you very much for your help. I have the tables set up as you have shown,
however I don't understand the query part. Do I need to set this up in Visual
Basic (of which my knowledge is limited, but I can do it) or can I set this
up in a simple query?

Instead of calling the rotation { D, D, N, N, 0, 0, 0, 0} I would call them
{0, 1, 2, 3, 4, 5, 6,7}, kind of having a 'primary key', if you prefer:

Rotations ' table name
RotID, RotName ' fields
0 D
1 D
2 N
3 N
4 null
5 null
6 null
7 null 'data

Then, it seems it is enough to track each team (rather than each employee in
each team):

Day0 ' table name
Team OnRotation ' field name
A 0
D 2
B 6
C 5
... ' data

so, to get the schedule at day + d, use:

SELECT team, rotName
FROM Day0 INNER JOIN Rotations
ON ((Day0.OnRotation + [d] ) MOD 8 ) = Rotations.RotID
WHERE NOT(rotName IS NULL)

(where [d] is a parameter, or from a driver table (a table having
integers from 0 to N ) )

Hoping it may help,
Vanderghast, Access MVP

I have a table in which I'd like to depict a rotating shift schedule. I can
generate a complete rotation in Excel and then import into Access, but I'd

[quoted text clipped - 88 lines]

schedule at a time, then be able to modify the employees in each slot as
needed.

 

[Michel Walsh]

That should go as a new query, in the SQL view.

With some modification:

SELECT team, rotName
FROM Day0 , Rotations
WHERE ( NOT(rotName IS NULL))
AND ((Day0.OnRotation + [d] ) MOD 8 ) = Rotations.RotID


you should be able to switch in graphic mode, and see how you could have
done it, graphically.


Since it is a query, you can use it where a query (and almost every where a
table) can be used.



Hoping it may help,
Vanderghast, Access MVP

Thank you very much for your help. I have the tables set up as you have
shown,
however I don't understand the query part. Do I need to set this up in
Visual
Basic (of which my knowledge is limited, but I can do it) or can I set
this
up in a simple query?

Instead of calling the rotation { D, D, N, N, 0, 0, 0, 0} I would call
them
{0, 1, 2, 3, 4, 5, 6,7}, kind of having a 'primary key', if you prefer:

Rotations ' table name
RotID, RotName ' fields
0 D
1 D
2 N
3 N
4 null
5 null
6 null
7 null 'data

Then, it seems it is enough to track each team (rather than each employee
in
each team):

Day0 ' table name
Team OnRotation ' field name
A 0
D 2
B 6
C 5
... ' data

so, to get the schedule at day + d, use:

SELECT team, rotName
FROM Day0 INNER JOIN Rotations
ON ((Day0.OnRotation + [d] ) MOD 8 ) = Rotations.RotID
WHERE NOT(rotName IS NULL)

(where [d] is a parameter, or from a driver table (a table having
integers from 0 to N ) )

Hoping it may help,
Vanderghast, Access MVP

I have a table in which I'd like to depict a rotating shift schedule. I
can
generate a complete rotation in Excel and then import into Access, but
I'd

[quoted text clipped - 88 lines]

schedule at a time, then be able to modify the employees in each slot as
needed.