Enter pay period based on work dates

[Lea Alexander]

I have two tables: Contracts and Payroll.  In the contracts table each record has a start and end date.  I want to auto enter the pay period code based on these dates. 

Each record in the payroll table has a pay period start and end date, as well as a Pay run ID. 

The tables are related: Contract primary key in Contract table to Contract foreign key in Payroll table.

I don't know if I should use If, Let, Look up??  I don't want to use Case, because then I'll have to change the calculation each year.  I'd rather add 12 records to the payroll table for the 12 months each year. 

These are two calc's I've tried, each calculations return "1".

If  ( Work_Date_Start ≥ Payroll::Pay_Period_Start  and Work_Date_End ≤ Payroll::Pay_Period_End; Payroll::Pay_Run_ID )

Lookup  ( Payroll::Pay_Run_ID ;  Work_Date_Start ≥ Payroll::Pay_Period_Start  and Work_Date_End ≤ Payroll::Pay_Period_End )

Thanks much!

[Lea Alexander]

Clarification - the tables are connected in the relationship graph, but the Payroll records are not related to the Contract records. 

[Fitch]

They're connected but not related? I will ponder that.

Meanwhile, one solution would be to make a new table occurrence and relationship using the start/end dates. That will make it really simple, you won't need a calculation, the relationship will handle it.