Relationship between a table of jobs and a schedule

[felix18807]

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.

[Gysbert_Wassenaar · Accepted solution]

The tables are related ok. Just change the sum expression to sum({<TimeOnJob={'*'}>} Active*7.5). That way records where TimeOnJob is missing won't be used to calculate the sum.