vlookup in a query

[tomeratz]

hi there
i have 3 tables

Emp_tbl

EmpID	EmpName
1	A
1	B

SalToEmp_tbl

EmpId	EmpHourelyRate	EmpSalChangeDate
1	20	01/01/2015
2	21	01/01/2015
2	23	01/03/2015

HouresDb_tbl

EmpID	WorkDate	WorkDateHoures
1	02/01/2015	8
2	04/01/2015	7
1	08/02/2015	5
2	21/02/2015	8
2	01/03/2015	8
1	05/03/2015	5
2	30/03/2015	6
2	02/04/2015	8.5

both the last two Joined to the first table with EmpID field.

my request for help:


i'm trying to creat the folowing querie

EmpName	WorkDate	WorkDateHoures	EmpSalChangeDate	EmpHourelyRate	DaySal
A	02/01/2015	8	01/01/2015	20	160
B	04/01/2015	7	01/01/2015	20	140
A	08/02/2015	5	01/01/2015	20	100
B	21/02/2015	8	01/01/2015	20	160
B	01/03/2015	8	01/03/2015	23	184
A	05/03/2015	5	01/03/2015	23	115
B	30/03/2015	6	01/03/2015	23	138
B	02/04/2015	8.5	01/03/2015	23	195.5

the EmpSalChangeDate field needs to be <= WorkDate Filed, and show the lower closest date.
I use Access 2010 windows 7 ultimate.

thanks

[CJ_London]

Join all three tables on EmpID and in your criteria for SalToEmp_tbl.EmpSalChangeDate put

(SELECT Max(EmpSalChangeDate) FROM SalToEmp_tbl AS T WHERE EmpID=SalToEmp_tbl.EmpID AND EmpSalChangeDate<=WorkDate)