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.