Compare two tables then update

[iamtrying]

Hi, I have two tables (DBO-employee & Biennial_Import). I would like to compare the FirstName, LastName, and Div fields of each table then update the "Div" field of DBO_employee table if the FirstName and LastName fields are equal and add a new record (FirstName, LastName, Div) to the DBO_employee table if there is no match.

I have tried the update query, but it deleted the contents of the FirstName and LastName fields

Thanks

 

[PHV]

Have a look here: thread701-1053292

UPDATE DBO_employee AS O RIGHT JOIN Biennial_Import AS N ON O.FirstName = N.FirstName AND O.LastName = N.LastName
SET O.FirstName = N.FirstName, O.LastName = N.LastName, O.Div = N.Div;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rac2]

Well the fact that you have not run from the building in tears indicates that you have a backup copy of your data. That is a good start.

Matching tables on peoples names is always a problem because different people sometimes have the same name. This you might check with a GROUP BY query.

SELECT FirstName, LastName, COUNT(*)
FROM OldTable
GROUP BY FirstName, LastName
HAVING COUNT(*) > 1

You can run this by going to Queries->New->Design View;
select the old table, Add and Close.

Then switch to SQL View and paste the above into the window. Substitute the real name of your table, of course.
Then switch to Datasheet View. If any names are listed then you have people with the same names. That is going to be a problem. Repeat this with the new table.

The SQL View and Datasheet View menu is the left-hand icon in the toolbar below the File item. At least that is where it is on my Access.

Let me know what you find and we can go on to the next step.

 

[iamtrying]

PHV,
The update statement you posted added the entire import table. I used the code from thread701-1053292 and it return the desired results. Thank you

Rac2,

I ran the select statement against both tables and there are duplicate last names, but that is fine because fathers and sons with the same name are employed. There are even some employees with same name and are not related. So, the code is fine. Thank you.