Re: Mutli Rosters within Division

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kev - Radio Man" <KevRadioMan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:550F3EDA-D469-424C-BE29-D3332488A46E@xxxxxxxxxxxxxxxx
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 spread*** 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.

.