I’m looking for some thoughts on looking up a value in one table between a range of dates in another table. The data is in two tables as follows:
Table 1: tblHR_JOBS
Field1 – EMPLID
Field2 – JOB_CODE
Field3 – EFFECTIVE_DATE
Field4 – MONTHLY_SALARY
Table 2: tblActual_Pay
Field1 – PAY_PERSON_ID (Same value as EMPLID - just different field name in the pay table)
Field2 – PAY_PERIOD_BEGIN_DATE
Field3 – PAY_PERIOD_END_DATE
Field4 – PAY_AMOUNT
What I’m trying do is join these tables in a query and do a lookup to find what the employee’s monthly salary was for each given pay period. i.e
DESIRED QUERY RESULT:
EMPLID
PAY_PERIOD_BEGIN_DATE
PAY_PERIOD_END_DATE
PAY_AMT
MONTHLY_SALARY
Will a DLOOKUP achieve what I’m attempting to do here? These tables are large and I know DLOOKUP could potentially suck the life out of the system. Any other thoughts on how I should go about this? Thanks for any insight.