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

    How to look up multiple fields in a record.

    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.