Mutli Rosters within Division

[Kev - Radio Man]

Hi, I am trying to improve on my access database for our employees within our
plant.
The biggest problem I have is that we have 6 different areas which have 6
different shift rosters.
We are a 24/7 business. I woud like a way of making up a roster to reflect
these, and allow the shift and crew to be assigned to each employee. Need to
be able to add new rosters as they are needed dependant on production
requirement.
I will attempt to include what we have.

Roster pattern 1.
12hr shifts, 4 crews, 2x days followed by 2x nights, 4x rest days, and 8 day
cycle.
These are listed as "KA", "KB", "KC" and "KD"
KA - DDNNRRRR
KB - NNRRRRDD
KC - RRDDNNRR
KD - RRRRDDNN
(Yes for some reason KB got out of sequence, one day it might get back to a
seq)

Roster pattern 2.
8hr shifts, 3 crews, Mon - Fri, Nights, Days, Afternoons. Weekends rest.
These are listed as "5A", "5B" and "5C"
5A week one "nights", week two "Afternoons and week three "Days"
5B " "Days", "Nights", "Afternoons"
5C " "Afternoons", "Days", "Nights"

Roster pattern 3.
8hr Days x 5, 10hr afternoons x 4, Mon - Fri
These are "LA" and "LB"
LA week 1 5x Days, week 2 4x afternoons.
LB week 1 4x afternoons, week 2 5x days.

Roster pattern 4.
8hr shifts, Days and afternoons, Mon - Fri
These are lsited as "5A' and "5B", but different to the above 3 shift crews.
5A week 1, Days, week 2 Afternoons.
5B week 1, Afternoons, week 2 Days.

I need to be able to build these tables which will allow me to look at an
operator, for a date, see what shift he/she will be on.
Also be able to change an operators shift pattern against a date, so when I
look back I can see the shift he/she was on at that time.

I have an operating spreadsheet in excel, and it does work, but is very hard
to manage, and would like to improve on it as I mentioned.
I do have a fair bit of the access operators database working, just refining
it.

Sorry for being long winded, just want to ensure as much detail is there.

Regards

Kevin.

 

[Allen Browne]

Kev, this is a very big question. I suspect you realize that we can only
give the broadest brush strokes as a response.

There are several other issues to resolve here. You currently have 3
patterns, which are presumably tied to the person's job descriptions (e.g.
if the person is currently a "machine A operator", that means they are on
roster pattern 2.) If so, and each employee has just ONE current job
description, then you can determine the pattern they are on from there.

Now do all the people on a pattern 2 start that pattern at the same time? If
so, you will have some mechanism for determining when each pattern starts,
e.g. a table of roster patterns with a starting date for each one. From
that, and the number of days in the cycle, you can determine each time the
cycle will start. And with the person's job description tied to the roster
pattern, you can now determine the shifts for each person.

If the patterns do not start at the same time for everyone, you will need
another mechanism for determining when each person's pattern starts. You
will also need to consider what happens with illness or leave, i.e. if the
person *normally* resumes the same pattern sequence after these kinds of
events.

In addition to this, you will need a table for recording when people are
unavailable. Each record records a worker, start date (required), end date
(optional), and reason for the proposed exclusion (e.g. annual leave.)

Now that you have a way to determining who would normally be on which
rosters, you need a table for the actual rosters. This will be an append
query statement that selects the usual roster records for the period, omits
those with exclusions, and inserts records into the actual roster table. You
can then edit these records as needed (e.g. where someone is ill and you
need to replace them with someone else.) If you include a batch number for
the append query, you will be able to remove the entire batch and re-do it
if needed.

As I say, that's only the broadest of brushstrokes. There will be many
aspects beyond that to consider, but hopefully it's a start. The concept of
a Cartesian Product query to show repeating sequences will also be useful.
You may even be able to use of of the concepts in the sample database here:
Recurring events
at:
http://allenbrowne.com/AppRecur.html

Hope that helps.

 

[Kev - Radio Man]

Allan,

Wow what a quick and in depth responce. Thank you for your comments.
To answer your questions, in all cases all employees start at the same time
for the shift.
For the 12hr operators they start at 07:00 and 19:00
For the weekly operators it's 00:00, 08:00, and 16:00.
One of the 2 shift weekly crews start at 08:00 and 16:00 only, and then the
last,
The other group then start at 07:00 and 11:00 (they have an over-lap from
11:00 to 15:00) But if I still list them as days and afternoons this will be
suvice.
Seeing your a Ozzy you will understand when I say I work for NZ Steel, a
member of Blue Scope Steel.

I am trying to make life for us (I am a shift supervisor) in managing our
manning requirements, who's at work, who's got leave under the manning rules
of permitted leave, and cover for OT.

Even though it is a big company, it is so hard to get these type of
requirements done, and to keep it to the KISS method.

Thanks greatly for your responce, Kevin.