Work scheduling/rostering in Access 2000

[mrigger]

Hi,

I wonder if someone could point me in the right direction with this one....

I need to be able to check at any time which person is working a shift on a
specific day, and whether they are working a day or night shift. In effect I
have 4 engineers, who work on shifts A B C & D. All the engineers work a 4
on, 4 off shift pattern, so for example if engineer A is working day shift,
engineer B will be working nightshift, C & D will be on rest days. The
pattern rotates every 4 days, so in the case of engineer A:

1st - days
2nd - days
3rd - days
4th - days
5th - rest
6th - rest
7th - rest
8th - rest
9th - nights
10th - nights
11th - nights
12th - nights
13th - rest
14th - rest
15th - rest
16th - rest
....................and so on.

I need to be able to pull up a form that tells me which engineer is working
which shift on a selected date -I hope I've explained myself properly. Can
this be calculated 'on the fly' using VBA & a reference table or lookup? I'd
prefer to do this by programming if possible.

Hope that someone can help.

Many thanks,

Mark

 

[Nikos Yannacopoulos]

Mark,

Each engineer has a 16-day cycle, and each engineer's cycle is phased 4
days later than the previous one; all that's required, really, is a
start date to anchor the whole calculation. Assuming this date is
Jan.01, 2005, you could use a VBA function like:

Function Engineer_Shift(sDate As Date, sEngineer As String) As String
StartDate = #01/01/2005# - 1
Select Case sEngineer
Case "A"
Case "B"
StartDate = StartDate + 8
Case "C"
StartDate = StartDate + 4
Case "D"
StartDate = StartDate + 12
Case Else
MsgBox "Wrong Engineer!"
Exit Function
End Select
Phase = (sDate - StartDate) Mod 16
Select Case Phase
Case 1 To 4
Engineer_Shift = "Day"
Case 5 To 8
Engineer_Shift = "Rest"
Case 9 To 12
Engineer_Shift = "Night"
Case 13 To 15, 0
Engineer_Shift = "Rest"
End Select
End Function

HTH,
Nikos