Merging data from 2 employees into one

[Anne]

The user always finds a way around everything designed.
My database involves imports from Quickbooks payroll. Since Quickbooks does
allow user to enter employees without any check for duplicate social
security numbers, I have been faced with having to merge data from 2
employeeIDs into one. I have manually used find and replace the employeeID
assigned in access, to combine the data. Then I delete the 2nd ID. I now
have the table fixed, where no duplicates are allowed in the social security
number field.

But I am interested to know, if I have employeeID 721 and EmployeeID 699,
both having the same social, is there a way in access to merge all data for
721 into data for 699 in an more automated way?

 

[John Nurick]

Hi Anne,

In principle this can be done with a single query, but in practice
(thanks to limitations in Access SQL) it's simpler in stages:

1) Create a table (let's call it SSID) with fields SSN and EmployeeID.
Use an append query to populate it with records for all the SSNs that
have multiple EmployeeIDs.

If you don't care which of the multiple EmployeeID values survive, a
query like this will do the job:

INSERT INTO SSID (SSN, EmployeeID)
SELECT SSN, MIN(EmployeeID)
FROM MyTable
GROUP BY SSN
HAVING COUNT(EmployeeID) > 1;

2) Create and run an update query that joins the two tables on the
social security number and updates EmployeeID in the main table with the
value from the SSID table.

3) Drop the SSID table.