I have two Labor reports I am trying to reconcile. One report is a payroll report with employee ID's, Date's worked, and Hours. The other report is a time ticket report to track labor activities, which also has employee ID's Hours worked, and the date. I am trying to reconcile these reports by importing them as tables, and then using the lookup function to subtract the hours p/date from one report to the other report by employee to identify employees with missing time tickets.

The problem I am having, is that when I create the lookup between the two reports, one report is summing ALL the historical hours for each employee and dumping all those hours into every date, This is making the reconciliation not work.

I have created a lookup between the employee ID and the dates between the reports

Cardinality for dates is many to many

Cardinality for employee ID I have tried one to one (what I thought it should be), but when that didn't work I also tried many to one, and many to many. None have worked.

Thanks for your help