1 Reply Latest reply: Mar 15, 2013 1:35 PM by Gysbert Wassenaar RSS

    Relationship between a table of jobs and a schedule

      I have two tables that I want to relate.

       

      The first table contains job information-

       

      JobNo     EmployeeNo     Date     TimeOnJob     TravelTime

       

      The second table has a list of days that an EmployeeNo should be active on-

       

      EmployeeNo     Date     Active

       

      How should these tables be related so I can guage the level of time spent working/travelling (TimeOnJob+TravelTime) vs amount of scheduled time (Active*7.5hrs)?

       

      The problem I have is when I use a composite key based on EmployeeNo and Date and try to sum scheduled time for week/month/year in a chart I get scheduled time for the days where an EmployeeNo has done a job.

       

      I think I'm missing something fundamental but at the moment I can't pin it down.

       

      Any Help would be appreciated.