Unanswered: Applying pay rates to applicable hours worked in Access 2013
I am working on a Microsoft Access 2013 database in which I have a table that contains a field for Employee Rate History ID, Employee ID, Rate of Pay, Effective Date and Reason Code. I have a master table that contains all worked hours (downloaded from ADP). The table has the Employee ID, Pay Date, Hours, Contract Number, etc. I have written queries that will identify the appropriate Rates of Pay for the selected date range. What I am trying to do is to then apply the appropriate Rate of Pay to the hours worked within my selected date range. The issue I am having is that the date range may include multiple Rates of Pay for that employee, i.e. employee received a raise within the selected date range. So, I have the Employee Wage History Table that contains multiple pay rates for employees and I need to apply the correct rates to a range of hours that encompass whatever date range I select. Basically, if I select a date range for January - February and an employee had received a pay increase during that time, I want to be able to identify which hours are paid at rate 1, which hours are paid at rate 2, etc., so that I can calculate expenses by contract. I am sure it is something simple, but I have been at it for a couple hours now and am tapped! Any help you can provide would be appreciated!!!
Applying pay rates to applicable hours worked in Access 2013 - SOLVED
Never mind. I figured it out. Needed to add an additional field to Employee Wage Rate History to account for both the Start Date and End Date for the wage rate. Once I did that, I was able to write an IIF statement to calculate if the Pay Rate fell between the Start and End Dates and then supply the applicable Wage for that period.