help with design

[Michelle]

Hi all

I have created a staff scheduling database which has actually been
rolled out. However, at the moment the user has to schedule teams to
specific hours by selecting the appropriate date and team and then
choosing a shift pattern from a combo box. However, it has now been
decided that there will be a pattern to the shifts the team works
which will roll round on a nine week basis.

So far I have created 2 extra tables in my database as follows:-

tblTeamShifts
================
ID - autonumber
TeamName
WK1
WK2
WK3
etc down to WK9 - these are lookup fields to tblShiftPatterns table
below - shiftID

tblShiftPatterns
================
ShiftID = primary key values = A, B, C, D etc
Monday
Tuesday
Wednesday
etc through to Sunday - these fields lookup to tblShifts table which
define a shift start and end time and a lunch start and end time.

My aim is to be able to identify for any date in the future (or past)
what shift a team will be working. WK1 in tblTeamShifts table
represents the week beginning 1 March 2004. Other functions in the
database require that the team shift data is always available, ie when
booking somebody on a half day leave need to know what shift they are
rostered to work so that can adjust their work times accordingly.

I would be grateful for any advice/help anyone can give me.

thanks
Michelle

[PC Datasheet]

Hi Michelle,

Here's a hint about designing tables --

Any time you see repeating fields in a table, that indicates your design is
incorrect and will lead to trouble. In your table, tblTeamShifts, you repeat
WK1, WK2, WK3, etc. In your table, tblShiftPatterns, you repeat Monday, Tuesday,
Wednesday, etc.

Back to the drawing board!
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com


"Michelle" <mi*************@poferries.com> wrote in message
news:72**************************@posting.google.c om...

Hi all

I have created a staff scheduling database which has actually been
rolled out. However, at the moment the user has to schedule teams to
specific hours by selecting the appropriate date and team and then
choosing a shift pattern from a combo box. However, it has now been
decided that there will be a pattern to the shifts the team works
which will roll round on a nine week basis.

So far I have created 2 extra tables in my database as follows:-

tblTeamShifts
================
ID - autonumber
TeamName
WK1
WK2
WK3
etc down to WK9 - these are lookup fields to tblShiftPatterns table
below - shiftID

tblShiftPatterns
================
ShiftID = primary key values = A, B, C, D etc
Monday
Tuesday
Wednesday
etc through to Sunday - these fields lookup to tblShifts table which
define a shift start and end time and a lunch start and end time.

My aim is to be able to identify for any date in the future (or past)
what shift a team will be working. WK1 in tblTeamShifts table
represents the week beginning 1 March 2004. Other functions in the
database require that the team shift data is always available, ie when
booking somebody on a half day leave need to know what shift they are
rostered to work so that can adjust their work times accordingly.

I would be grateful for any advice/help anyone can give me.

thanks
Michelle

[Michelle]

OK - thanks for that. How about:-

tblShiftPattern
================
ShiftID = autonumber
ShiftPatternID = A,B,C,D,E,F,G,H,I
ShiftDate
Shift = lookup to tblShift which determines start and end time of shift,
and start and end time of lunch break

tblTeamShifts
=============
TeamName - lookup to tblTeams
ShiftDate
ShiftPatternID - lookup to tblShiftPattern, ShiftPatternID

thanks
michelle

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[PC Datasheet]

Here are the tables you need:
TblStaff
StaffID
FName
LName

TblStaffTeam
StaffTeamID
StaffID
TeamName

TblShiftPattern
ShiftPatternID
WeekDay
WeekNum
StartTime
EndTime
LunchStartTime
LunchEndTime

TblTeamShiftSchedule
TeamShiftScheduleID
StaffTeamID
ShiftPatternID

Since a team's schedule rolls around on a nine week schedule, you need to have
nine records in
TblTeamShiftSchedule for each team.

Next you need to develop a function that calculates what week number a selected
date falls into. The calculation needs to include the week beginning March 1,
2004 since by definition that is week 1. Since the schedule rotates through nine
weeks, the week number can not be associated with the calendar week number
because 52 is not evenly divided by 9.

Once you have done the above, then to identify for any date what shift a team
will be working, you use DLookup on a query that joins TblShiftPattern and
TblTeamShiftSchedule for ShiftPatternID with a where clause containing the
function that calculates what week number a selected date falls into.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Michelle" <mi*************@poferries.com_NOSPAM> wrote in message
news:40*********************@news.frii.net...

OK - thanks for that. How about:-

tblShiftPattern
================
ShiftID = autonumber
ShiftPatternID = A,B,C,D,E,F,G,H,I
ShiftDate
Shift = lookup to tblShift which determines start and end time of shift,
and start and end time of lunch break

tblTeamShifts
=============
TeamName - lookup to tblTeams
ShiftDate
ShiftPatternID - lookup to tblShiftPattern, ShiftPatternID

thanks
michelle

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Michelle]

Thanks very much

That should keep me busy for a while

Will post again to let you know how I am getting on.

Cheers
Michelle

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!