8 Replies Latest reply on Mar 31, 2016 1:33 AM by macaque

    How to look up multiple fields in a record.


      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.