AnsweredAssumed Answered

How to look up multiple fields in a record.

Question asked by macaque on Mar 28, 2016
Latest reply on Mar 31, 2016 by macaque

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)

e.g.

 

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

e.g.

 

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.

Outcomes