Match multiple values in a dataset

[NeilPB]

I have a table called tmp_table2 which holds historical data for hours worked by various people on a project. I need to then add a charge rate to the Rate field from another table called Staff. The problem is that the charge rate varies from year to year so there are multiple entries for each person in the staff table with a rate for each year so I need to read the Name and Year fields for each record in tmp_table2 and then find the matching Name and Year records in the Staff table. When a match is found the Rate from the Staff table needs to be inserted into tmp_table2.
I've tried to do this with DLookup in a query but it doesn't give me the correct results.
Any help would be appreciated.

 

[SkipVought]

Hi,

Join the two tables on staff and year.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

A starting point (SQL code):
UPDATE tmp_table2 T INNER JOIN Staff S ON T.Name=S.Name AND T.Year=S.Year
SET T.Rate=S.Rate;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NeilPB]

Thank you both for your suggestions but I've now figured out the problem with my DLookup and I am now getting the correct answer. Both tables had the year in the format 2009/10 with an input mask to insert the '/'. For some reason one table stored the '/' in the data and the other didn't, so even though both displayed the same when I compared the 2 fields they never matched. Data is now cleaned so I am getting the right answers.

Neil

 

[PHV]

Anyway, I'd avoid DLookUp if a pure SQL solution works ...