Determine X for Table 1 based on Table 2

  • Thread starter Thread starter Juneya
  • Start date Start date
J

Juneya

I have two tables. Table1 is Current Employee Records.
Table 2 is Employee Rate History. Table1 has EmployeeID
and Date_Worked. Table2 has EmployeeID, Effective_Date,
and Rate.

I need to be able to determine the rate for the date
worked.

For Instance;
Table1
EmpID Date_Worked
abc 2/1/2004
abc 6/1/2004

Table2
EmpID Effective_Date Rate
abc 1/1/2004 10
abc 5/1/2004 20

I need to be able to show the "Rate" from Table2 for work
done on 2/1/2004 was at rate 10 and work done on 6/1/2004
was at 20. I am sure there is a simple solution to this
however I have yet to find it.
 
I find it's much easier if you add an Expiry_Date. Set the Expiry Date to
Null initially, or to the Effective_Date of the next record when it's no
longer effective.

Then, your query will be something like

SELECT Table1.EmployeeID, Table1.DateWorked, Table3.Rate
FROM Table1 INNER JOIN Table2
ON Table2.Effective_Date <= Table1.DateWorked
AND (Table2.Effective_Date IS NULL
OR Table2.Effective_Date >= Table1.DateWorked)

Note that you won't be able to build this query in the query grid. The
easiest way is to join the tables on EffectiveDate vs. DateWorked, and then
go in and edit the ON clause that gets built for you.
 
Back
Top