Append Query key violation 2

[splats]

Hello

I have a command button on a form that runs an append query and then an update query in Access 2007. The update query works no problem. However, the Append Query does not work. Here is the code that I am using. Anyone have any ideas to make it work?

Append Query (this one results in a key violation error)
INSERT INTO tblPersonnel ( EmployeeID, FirstName, MiddleName, LastName, Gender, SpouseName, StreetAddress, City, Province, PostalCode, Country, HomePhone, CellPhone, Department, Occupation, Site, BirthDate, StartDate, RetirementDate )
SELECT ImportData.EmployeeID, ImportData.FirstName, ImportData.MiddleName, ImportData.LastName, ImportData.Gender, ImportData.SpouseName, ImportData.StreetAddress, ImportData.City, ImportData.Province, ImportData.PostalCode, ImportData.Country, ImportData.HomePhone, ImportData.CellPhone, ImportData.Department, ImportData.Occupation, ImportData.Site, ImportData.BirthDate, ImportData.StartDate, ImportData.RetirementDate
FROM [qu-PersonnelImport] INNER JOIN ImportData ON [qu-PersonnelImport].EmployeeID = ImportData.EmployeeID;


Update Query (this one works fine!)
UPDATE ImportData INNER JOIN [qu-PersonnelImport] ON ImportData.EmployeeID = [qu-PersonnelImport].EmployeeID SET ImportData.EmployeeID = [ImportData].[EmployeeID], [qu-PersonnelImport].FirstName = [ImportData].[FirstName], [qu-PersonnelImport].MiddleName = [ImportData].[MiddleName], [qu-PersonnelImport].LastName = [ImportData].[LastName], [qu-PersonnelImport].Gender = [ImportData].[Gender], [qu-PersonnelImport].SpouseName = [ImportData].[SpouseName], [qu-PersonnelImport].StreetAddress = [ImportData].[StreetAddress], [qu-PersonnelImport].City = [ImportData].[City], [qu-PersonnelImport].Province = [ImportData].[Province], [qu-PersonnelImport].PostalCode = [ImportData].[PostalCode], [qu-PersonnelImport].Country = [ImportData].[Country], [qu-PersonnelImport].HomePhone = [ImportData].[HomePhone], [qu-PersonnelImport].CellPhone = [ImportData].[CellPhone], [qu-PersonnelImport].Department = [ImportData].[Department], [qu-PersonnelImport].Occupation = [ImportData].[Occupation], [qu-PersonnelImport].Site = [ImportData].[Site], [qu-PersonnelImport].BirthDate = [ImportData].[BirthDate], [qu-PersonnelImport].StartDate = [ImportData].[StartDate], [qu-PersonnelImport].RetirementDate = [ImportData].[RetirementDate];


TYVM

Tina

 

[PHV]

Seems like your append query try to insert an already existing EmployeeID.

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

 

[dhookom]

You can troubleshoot by removing about 1/2 the records (controlled) and attempt the query. If it works then the duplicate was in 1/2 you removed. Keep working it until you find the issue(s).

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

In order to find the duplicates employees:

SELECT I.EmployeeID, I.FirstName, I.MiddleName, I.LastName, I.Gender, I.SpouseName
, I.StreetAddress, I.City, I.Province, I.PostalCode, I.Country
, I.HomePhone, I.CellPhone, I.Department, I.Occupation, I.Site
, I.BirthDate, I.StartDate, I.RetirementDate
FROM ([qu-PersonnelImport] Q
INNER JOIN ImportData I ON Q.EmployeeID = I.EmployeeID)
INNER JOIN tblPersonnel P ON I.EmployeeID = E.EmployeeID

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

 

[splats]

Thank you PHV and dhookum for your assistance. I was able to rectify the issue. I found that in the table that held the Personnel data, had a couple of fields that has the setting "Allow Zero Length" set to No. As well, I recreated the queries with the following. They both run off a single command button and work great!

Update Query
UPDATE ImportData INNER JOIN [qu-PersonnelImport] ON ImportData.EmployeeID = [qu-PersonnelImport].EmployeeID SET ImportData.EmployeeID = [ImportData].[EmployeeID], [qu-PersonnelImport].FirstName = [ImportData].[FirstName], [qu-PersonnelImport].MiddleName = [ImportData].[MiddleName], [qu-PersonnelImport].LastName = [ImportData].[LastName], [qu-PersonnelImport].Gender = [ImportData].[Gender], [qu-PersonnelImport].SpouseName = [ImportData].[SpouseName], [qu-PersonnelImport].StreetAddress = [ImportData].[StreetAddress], [qu-PersonnelImport].City = [ImportData].[City], [qu-PersonnelImport].Province = [ImportData].[Province], [qu-PersonnelImport].PostalCode = [ImportData].[PostalCode], [qu-PersonnelImport].Country = [ImportData].[Country], [qu-PersonnelImport].HomePhone = [ImportData].[HomePhone], [qu-PersonnelImport].CellPhone = [ImportData].[CellPhone], [qu-PersonnelImport].Department = [ImportData].[Department], [qu-PersonnelImport].Occupation = [ImportData].[Occupation], ImportData.SiteLocation = ImportData.[SiteLocation], [qu-PersonnelImport].BirthDate = [ImportData].[BirthDate], [qu-PersonnelImport].StartDate = [ImportData].[StartDate], [qu-PersonnelImport].RetirementDate = [ImportData].[RetirementDate];

Append Query
INSERT INTO tblPersonnel ( EmployeeID, FirstName, MiddleName, LastName, Gender, SpouseName, StreetAddress, City, Province, PostalCode, Country, HomePhone, CellPhone, Department, Occupation, SiteLocation, BirthDate, StartDate, RetirementDate )
SELECT ImportData.EmployeeID, ImportData.FirstName, ImportData.MiddleName, ImportData.LastName, ImportData.Gender, ImportData.SpouseName, ImportData.StreetAddress, ImportData.City, ImportData.Province, ImportData.PostalCode, ImportData.Country, ImportData.HomePhone, ImportData.CellPhone, ImportData.Department, ImportData.Occupation, ImportData.SiteLocation, ImportData.BirthDate, ImportData.StartDate, ImportData.RetirementDate
FROM ImportData;