raziel3
Registered User.
- Local time
- Today, 12:20
- Joined
- Oct 5, 2017
- Messages
- 275
Hello all, I've been searching the internet for a solution for a while now. Query and Tables involved
qryPayDetail
EMPID, WorkDay, Basic, Overtime, CurRate
tblERates
EMPID, EffectiveDate, Rate
I want to be able to fill CurRate with current employee rate based on the WorkDay.
For example Records in tblERates:
1. EMPID 1 EffectiveDate 1/1/19 Rate 25 (m/d/yyyy)
2. EMPID 1 EffectiveDate 5/1/19 Rate 30
if WorkDay was 3/31/19 the Rate would be 25
if WorkDay was 5/31/19 the Rate would be 30
I was trying a dlookup but after reading up of similar problems, Dlookups scans the whole table making it inefficient. Also the Dlookup duplicates data in qryPayDetail so I am getting 1 WorkDay and the 2 Rates are being populated.
Can anyone suggest a subquery?
qryPayDetail
EMPID, WorkDay, Basic, Overtime, CurRate
tblERates
EMPID, EffectiveDate, Rate
I want to be able to fill CurRate with current employee rate based on the WorkDay.
For example Records in tblERates:
1. EMPID 1 EffectiveDate 1/1/19 Rate 25 (m/d/yyyy)
2. EMPID 1 EffectiveDate 5/1/19 Rate 30
if WorkDay was 3/31/19 the Rate would be 25
if WorkDay was 5/31/19 the Rate would be 30
I was trying a dlookup but after reading up of similar problems, Dlookups scans the whole table making it inefficient. Also the Dlookup duplicates data in qryPayDetail so I am getting 1 WorkDay and the 2 Rates are being populated.
Can anyone suggest a subquery?