I will be inputting time sheets for a two year period.  How do I check on he effective pay rate for the date of the time sheet.

I have two Tables - employees and time sheets

Please describe your problem in more detail.

I have three tables - Employees which contains a history of their pay rates and labor burden rates and their effective dates

the second table is a time sheet line entry that includes the employee, hours worked, job worked and charge for those hours using their pay rate plus labor burden times hours.  This info is used by a third table to report on Job costs by employee on a specific job.

 

The time sheets for 2013 and 2014 are going to be entered.  I need to use the correct rates for the time being entered. I want to avoid repeating fields if I can.  I just can't seem to figure out how to do this.

I am afraid this is still not quite clear:

 

[table] Employees which contains a history of their pay rates and labor burden rates and their effective dates

 

Do you mean there is more than one record per employee in this table? An example of the data could be useful.

 

 

the second table is a time sheet line entry that includes the employee, hours worked, job worked and charge for those hours using their pay rate plus labor burden times hours.

 

If the charge using the correct(?) pay rate is already there, then what exactly is the task you are asking us to help you with?

I am importing historical time data into a new program.  The charges need to be calculated based on the rates effective at the time of the time sheet's end date.  I am still in the process of establishing the tables to handle this.  Currently the employee table contains  only the current info for Employee, Effective Date, Reg Rate, OT Rate, Labor Burden Rate, Adjusted Reg Rate(Reg Rate +(Reg Rate*Labor Burden), Adjusted OT Rate(OT Rate+(OT Rate* Labor Burden).

 

The Time Sheet records contain Employee, Payroll End Date, Hours, Hours Type(REG or OT), Job Number,Job Charge(Calculated Type of Hours Adjusted Rate * hours)(Lookup)

 

Since this is historical data the pay rates change and the Labor Burden changes at least annually. Depending on how the calculation to use the correct rates determines how to set up the Employee Table.

 

I hope this explains what I a trying to do.  This is part of a much larger File which tracks incoming calls, appts, sales and Jobs.

The charges need to be calculated based on the rates effective at the time of the time sheet's end date.

 

As this is practically the only thing that I fully understand from your post, let me hold on to that: if the time sheet does not already contain the correct rate, you need to look it up from somewhere else. Ideally, that somewhere would be a table of EmployeeRates, with (at least) the following fields:

 

• EmployeeID

• FromDate

• Rate

 

Given this relationship:

 

Timesheets::EmployeeID = EmployeeRates::EmployeeID

AND

Timesheets::EndDate ≥ EmployeeRates::FromDate

 

with the records from EmployeeRates sorted by FromDate, descending, each record in Timesheets will "see" the employee's rate that was in effect at the relevant date.

 

 

Currently the employee table contains  only the current info for Employee,

 

Well then this is not a good source for the lookup. The EmployeeRates table I have described earlier needs to have multiple records for each employee - one record for each rate in the employee's history.

Sorry I could not explain it better but you were able to figure out what I needed. I did as you suggested and it works like a charm.  Thank you so much.