Design Suggestions Please

[linkaccess]

I'm setting up a scheduling database and need help in setting days off. A record is generated for each employee [employeetableID] for the entire year. The record contains their shift assignment [shifttableID], day off rotation [dayofftableID] work status [statustableID-Working, Off, Training, Special] and a date for each day of the year (this is needed to plan out vacations and other time off). each status is automatically set to "working." I then want to set their days off based on their day off rotation. There are five different day off rotations. The ones that are always set (e.g. saturday and sunday) are easy enough to figure out. The day off rotation I am having trouble with is the "10 hour kelley rotation".

We have two kelly rotations:
1st one looks like this
1st week off Sunday & Saturday
2nd week off Sunday, Monday, Tuesday,
3rd week off Monday, Tuesday, Wednesday, Thursday
4th week off Thursday, Friday, Saturday

2nd one looks like this
1st week off Monday, Tuesday, Wednesday, Thursday
2nd week off Thursday, Friday, Saturday
3rd week off Sunday, Saturday,
4th week off Sunday, Monday, Tuesday

New schedules will always be set up to start on a Sunday.

Any ideas on the best way to set this up?

 

[AccessMSSQL]

If I understand your need, you want to generate a record for each day of the year (365 days) for each new employee and based on whether the kelly schedule is schedule 1 or schedule 2, you want the work status to say Working or Off based on this?
So ideally, you would end up with a table like this:
Employee1 2/3/2013 (Sun) Off
Employee1 2/4/2013 (Mon) Working
Employee1 2/5/2013 (Tue) Working
Employee1 2/6/2013 (Wed) Working
etc...
?? Just clarifying.


There are probably a few different methods for accomplishing what you need.

 

[linkaccess]

Your example is just what i have. I just can't figure out the best way to set it up.

 

[AccessMSSQL]

Numerous ways to go about it. Are you comfortable using VBA?

 

[linkaccess]

I'm somewhat comfortable with it.

 

[AccessMSSQL]

First thing I would do is create a table to store your kelly rotation data.

TABLE: tblKellyRotation
Fields:
KellyRotationID autonumber primary key
Day int,
Week int
Status
RotationType int

Create a record in this table for Every day and every status in the 4 week period.
You will end up with 28 records in this table for each different schedule type
RotationType is the schedule type (1 or 2).

When you have this set up you can than proceed to create the employee work schedule based on a starting date that is a Sunday and the rotation type. The above table assumes 1 = Sunday.

You would have to loop through each day of the year from the start date, keep track of the week number and then re-set the week after 4 weeks have been scheduled. For each date you would lookup the day and week in the schedule table and pull the status from it to populate the employee schedule.

If you need help writing the VBA let me know. You would probably need a function that looks in the schedule table for each date, something like this:

Public Function GetSchedule (Day int, Week int, RotationType int) as String
Dim rs as dao.recordset
dim strSQL as string
 
strSQL = "SELECT * FROM tblKellyRotation WHERE Day = " & day 
strsql = strsql & "  and Week = " & week 
strsql = strsql &  " and RotationType = " & RotationType
set rs = currentdb.openrecordset(strsql,dbopendynaset,dbopendynamic)
if not rs.eof then
GetSchedule = rs("Status")
end if
rs.close
set rs =nothing

You would call this function for each date in the employees schedule.

 

[linkaccess]

Thanks for the help! I was just thinking (making this harder for myself). What if I set up a table like this:
DayOffRotationID (PK)
DayOffRotationName
Sunday1 - yes/no
Monday1 - yes/no
Tuesday1 - yes/no
and continue the days until it would end
Friday4 - yes/no
Saturday4 - yes/no
yes/no would be a checkbox. yes = work no = off

I'm leaning towards this now because in the future another rotating shift may be added with different days off. This would also make it easier for the user to define their own four week off rotation or change days to a current one.

Do you think something like this could work?

Sorry in advance for wasting your time earlier.

 

[AccessMSSQL]

This is similar to what I was suggesting. But what's the 1 and 4 after the day name? How are you going to match up the date with the day name in your table. For instance, if your start date is Sunday 2/3/2013 you would have to convert that date to the day name and then match it up with the dayoffrotationname in your table. If you set it up as an integer you would just need to use Weekday(#2/3/2013#) to return 1 for Sunday, 2 for Monday, etc. and then increment your date by one: WeekDay(DateAdd("d",#2/3/2013",1)) to go to the next day, etc.

 

[jdraw]

Be cautious of Access reserved words Day
Better to prefix/suffix such field names to be more focused to your table.

Reserved words at
http://allenbrowne.com/AppIssueBadWord.html