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.