473,320 Members | 1,867 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Roster prediction

Hi all,
The background to this problem is that I have been asked to create a Db
that monitors holidays within the warehouse. What the users wish to do is to
type in a colleague number (unique) and then the DB will say either "yes" or
"no" if it is ok to authorise. The yes or no will be based on how many other
people (as a percentage) will be rostered in on that day and how many have
holidays booked on that day.

To do this, I need to know who will be in on what day up to 2 years in
advance.

The roster rolls over every 8 weeks and is broken into 5 teams and 8 weeks.
So I have 740+ employees split between 40 shift patterns.
The employee table will have the unique colleague number along with which
team and week they are assigned to.

Using excel, I worked out the shift patterns and split it into 4 columns,
shift date, team, week & on/off. This equated to over 30.000 records going
up to 2007. When this is multiplied by the number of employees, the record
count goes up to over 2,000,000 records!!!!! This is obviously a bad design
but I cant think of a way to predict what so many people will be doing in
the future on so many shift patterns.

Example of the shift patterns will be:

Team 1
Sat Sun Mon Tue Wed Thur Fri
Week 1 Earlies Earlies Earlies Off Off Lates Lates
Week 2 Lates Lates Lates Off Earlies Earlies Earlies
Week 3 Earlies Off Lates Lates Lates Lates Lates
Week 4 Off Off Earlies Earlies Earlies Earlies Earlies
Week 5 Off Off Lates Lates Lates Lates Lates
Week 6 Lates Off Off Earlies Earlies Earlies Earlies
Week 7 Earlies Earlies Off Lates Lates Lates Lates
Week 8 Off Earlies Earlies Earlies Earlies Earlies Earlies

An 8th of the people assigned to team 1 will be on week 1, another to week 2
and so forth.
Could anyone suggest a way in which I can predict what shift an employee
will be working without have a table with all the data in it?

Any help would be gratefully accepted as I am at a standstill.

Thanks in advance,

Mark
Nov 12 '05 #1
1 2165
Mark Reed wrote:
Hi all,
The background to this problem is that I have been asked to create a Db
that monitors holidays within the warehouse. What the users wish to do is to
type in a colleague number (unique) and then the DB will say either "yes" or
"no" if it is ok to authorise. The yes or no will be based on how many other
people (as a percentage) will be rostered in on that day and how many have
holidays booked on that day.

To do this, I need to know who will be in on what day up to 2 years in
advance.

The roster rolls over every 8 weeks and is broken into 5 teams and 8 weeks.
So I have 740+ employees split between 40 shift patterns.
The employee table will have the unique colleague number along with which
team and week they are assigned to.

Using excel, I worked out the shift patterns and split it into 4 columns,
shift date, team, week & on/off. This equated to over 30.000 records going
up to 2007. When this is multiplied by the number of employees, the record
count goes up to over 2,000,000 records!!!!! This is obviously a bad design
but I cant think of a way to predict what so many people will be doing in
the future on so many shift patterns.

Example of the shift patterns will be:

Team 1
Sat Sun Mon Tue Wed Thur Fri
Week 1 Earlies Earlies Earlies Off Off Lates Lates
Week 2 Lates Lates Lates Off Earlies Earlies Earlies
Week 3 Earlies Off Lates Lates Lates Lates Lates
Week 4 Off Off Earlies Earlies Earlies Earlies Earlies
Week 5 Off Off Lates Lates Lates Lates Lates
Week 6 Lates Off Off Earlies Earlies Earlies Earlies
Week 7 Earlies Earlies Off Lates Lates Lates Lates
Week 8 Off Earlies Earlies Earlies Earlies Earlies Earlies

An 8th of the people assigned to team 1 will be on week 1, another to week 2
and so forth.
Could anyone suggest a way in which I can predict what shift an employee
will be working without have a table with all the data in it?

Any help would be gratefully accepted as I am at a standstill.

Thanks in advance,

Mark


I don't have a formula but I'm sure one can be devised.

I'd probably have a lookup table of your week1/8 so folks no what the
schedule is for that particular team/week once the calc determines the week.

I would have a StartDate someplace. IOW, you need a date to start
from/compare to a future date. For example, you could use the HireDate
as the start. You would also want to store which week the person starts
on. For example, when hired, this person is on Team 1, week 6.

Note: If the Week the person starts on based on the hire date can be
calculated, you could also store another start date to a static table.
This table could simply have 1 field, 1 record that stores a date. Then
you could subtract that start date from the hire date to determine the
week that this person starts in. (I don't know how you assign a person
to the team/start week)

You would probably need DateDiff() to calculate the number of days from
start to future.
intWeeks = int(DateDiff("d",[HireDate],[FutureDate])/7)
You now have the number of weeks between hire date/future date.

You would want to know how many timeweeks to increment by.
intTimeWeeks = intWeeks MOD 8

This will give you a remainder from the number of weeks divided by 8.
Thus if person was in week1 when hired and the result of weeks mod 8 was
0, then he would be in week1 in the future. If the mod was 7, it would
be week 8.

You'd have to make an adjustment for those, when hired, are on a diffent
shift. Let's take a look at Week5. If intTimeWeeks had a mod of 0 to
3, you could add that to get week5-8. But if the mod result was 4-7,
you need to adjust to get week1-4. Maybe something like
WeekTeamAtFutureDate = [WeekTeamAtStartOfHire] + intTimeWeeks
If WeekTeamAtFutureDate > 8 then
WeekTeamAtFutureDate = WeekTeamAtFutureDate -8
Endif

I think if you take these concepts and fiddle around with it a bit you
should be able to cobble something together. You also need to adjust if
a person starts on week1 and is "promoted" to Week7 in the future due to
an organization change.


Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: JamesLomuscio | last post by:
To all who know C++, Im working on a prog that will be able to predict the weather, its too complicated to go into here, but I do need some help. I need to grab all of the information in the...
1
by: Mark Reed | last post by:
Hi all, The background to this problem is that I have been asked to create a Db that monitors holidays within the warehouse. What the users wish to do is to type in a colleague number (unique) and...
4
by: news.microsoft.com | last post by:
In my engineering class we're discussing microprocessor branch predictors. Is it possible to write a Windows application (anything whatsoever) that would allow me see how branch prediction is done...
0
by: Mark | last post by:
Hi, I have been asked to create a roster application for a local company. Rather then reinventing the wheel, does anyone know of some .NET open source roster software. I have looked at...
1
by: robert d via AccessMonster.com | last post by:
I'm trying to determine who is linked to the backend. I'm using the Microsoft code on the Jet User Roster that uses ado to develop a list. The front end and backend are secured. This code is to...
0
by: ImgDev | last post by:
We have an application where we process a long series of items (10,000 - up to 1,000,000). Each item has a short time frame within which several complicated processes MUST happen - An item passes a...
1
by: Blinky | last post by:
Hi all, I am about to create a web application using asp and vbs to build an online roster for a department of 25 people. The roster covers some 10 shifts a day, 7 days a week always starting on...
6
by: rdemyan via AccessMonster.com | last post by:
I've implemented the Jet Roster code for determining how many users are logged into the backend file. There are many code examples and I've copied them. Also, I've checked at least 20 times...
0
by: ohmmega | last post by:
hey. i use a IR-control for my pc and work on an on-screen-display. therefore i would like to have a T9 input. i searched some smartphone postings, but there was nothing usefull. exact...
0
by: frostfire | last post by:
hey..first time on the site so i hope i get through... i'm having trouble writing a program to perform the T9 predictive text..its supp to just list the words that can be created from the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.