I have an access database that has linked SQL tables. One of the tables (dbo_EmployeeInformation) has employee records that occasionally need to be updated. I have an extract of current data that I have tried linking into the database as well as creating a table in the database where I paste the information.
I don't want to replace the entire content of dbo_EmployeeInformation, because I would loose historical data for employees that have left the company, I just need to compare the new data with the old to see if anything changed (Name, Address, Manager, etc) and update dbo_EmployeeInformation with these changes.
I created an update query that relates the two tables on their primary keys:
When I run the query I get the error:
Here is the query I am using:
Any help would be greatly appreciated.
I don't want to replace the entire content of dbo_EmployeeInformation, because I would loose historical data for employees that have left the company, I just need to compare the new data with the old to see if anything changed (Name, Address, Manager, etc) and update dbo_EmployeeInformation with these changes.
I created an update query that relates the two tables on their primary keys:
[dbo_EmployeeInformation].[strEmployeeID] = [tblEmployeeInformation_Update].[Employee ID]
When I run the query I get the error:
I have checked both tables and there are no duplicates in the primary key fields.Single-Row update/delete affected more than one row of the linked table. Unique index contains duplicate values.
Here is the query I am using:
Code:
UPDATE dbo_EmployeeInformation INNER JOIN tblEmployeeInformation_Update ON dbo_EmployeeInformation.strEmployeeID = tblEmployeeInformation_Update.[Employee ID] SET dbo_EmployeeInformation.strActiveStatus = [tblEmployeeInformation_Update].[Active Status], dbo_EmployeeInformation.strOnLeave = [tblEmployeeInformation_Update].[On Leave], dbo_EmployeeInformation.strEmployeeName = [tblEmployeeInformation_Update].[Employee Name], dbo_EmployeeInformation.strFirstName = [tblEmployeeInformation_Update].[First Name], dbo_EmployeeInformation.strLastName = [tblEmployeeInformation_Update].[Last Name], dbo_EmployeeInformation.strMiddleName = [tblEmployeeInformation_Update].[Middle Name], dbo_EmployeeInformation.strPrimaryAddressFull = [tblEmployeeInformation_Update].[Primary Address - Full], dbo_EmployeeInformation.strAddress1 = [tblEmployeeInformation_Update].[Address 1], dbo_EmployeeInformation.strAddress2 = [tblEmployeeInformation_Update].[Address 2], dbo_EmployeeInformation.strHomePhone = [tblEmployeeInformation_Update].[Home Phone], dbo_EmployeeInformation.strJobTitle = [tblEmployeeInformation_Update].[Job Title], dbo_EmployeeInformation.strLocationCode = [tblEmployeeInformation_Update].[Location Code], dbo_EmployeeInformation.strLocationName = [tblEmployeeInformation_Update].[Location Name], dbo_EmployeeInformation.strDirectManager = [tblEmployeeInformation_Update].[Direct Manager], dbo_EmployeeInformation.strHRBusinessPartner = [tblEmployeeInformation_Update].[HR Business Partner];
Any help would be greatly appreciated.