I’m trying to build a database of shift patterns for a duty roster.
So far I have two tables - one is called “SHIFTS” and contains the details of various shifts:
ShiftName, StartTime, EndTime and Duration (calculated)
Days 08:00 16:00 8
Lates 09:30 17:30 8
Evenings 15:00 22:00 7
Nights 22:00 08:00 10
The other table “PATTERNS” will contain records of various shift patterns for seven days to build a working week:
PatternName, Mon, Tue, Wed, Thu, Fri, Sat, Sun, TotalHours
Alternate_Days Day Lates Day Lates Day "" "" 40
I’ve linked the two tables so that as the user enters shift names, they appear on a pick list that is looked up from the Shifts table. I now need to use those shift names to look up the shift duration so I can add them together to give a week’s total for the shift pattern.
I’ve tried using lookup but that only seems to work if there is a single match field in a record and I have up to 7. Any ideas as to how this can be achieved? Thanks.