Help with Append Query

[KristenD]

I am appending a field from one table to another and it will not work. It is telling me that it either has violations or duplicate output destination.

When I run it through as a Select Query the information is correct, but as soon as I make it an Append Query it will not work.

INSERT INTO tblEmp ( EmpType )
SELECT tblEmp.*, tblEmpInfo.EmpTypes
FROM tblEmp INNER JOIN tblEmpInfo ON tblEmp.EmployeeID = tblEmpInfo.EmpIDFK;

That is the SQL for the Append Query, what am I doing wrong?

Thank you for your help!!

 

[Trevor G]

When you looked to create the query did you double click the "*" to select all the fields? Perhaps if you select all the fields less the "*" and drag them into the query grid and then set your criteria and change to an append it will work.

 

[KristenD]

INSERT INTO tblEmp
SELECT tblEmp.*
FROM tblEmp INNER JOIN tblEmpInfo ON tblEmp.EmployeeID = tblEmpInfo.EmpIDFK;

I switched it to this, because I thought maybe since there was an empty field it wasn't working. But the data still won't append and it is adding duplicate rows when I run it as a select query.

 

[Trevor G]

Try my suggestion please.

 

[KristenD]

That did not work either.

All I'm looking to do is append the one field "EmpType" from tblEmpInfo to tblEmp. Am I misunderstanding something?

The tables are related by the employee ID. When I run it as a Select query everything is fine all the records match up correctly but as soon as I make it an Append Query it tells me there are violations it won't work.

 

[KristenD]

Am I using the wrong query to accomplish what I am trying to do??

I would like to take a field from one table and move it to another table and then delete it in the original table. No matter what I have done I CANNOT get the the field and the information into the new table.

Should I being doing an update query rather than an append query? Or am I just not matching the fields up correctly in the query. I can run a Select query and it gives me what I want but as soon as I change it to either an update or append query it will not work.

PLEASE HELP!!
Thank you!!

 

[boblarson]

It sounds like you really need an Update query instead.

 

[KristenD]

I've tried the update query as well and it keeps telling me I need a destination field. I want to move EmpType from tblEmpInfo to tblEmp.

UPDATE tblEmp INNER JOIN tblEmpInfo ON tblEmp.EmployeeID = tblEmpInfo.EmpIDFK SET tblEmp.EmpType = [tblEmpInfo].[EmpType];

Is this correct?

 

[boblarson]

interesting. I set up a query exactly as you have it and it worked fine for me. Could there be a wrongly named field or maybe a field that is set with a lookup on it at table level and so it is looking for something else instead?

 

[KristenD]

I'm not sure what happened but it finally worked. There are no look ups at the table level. Based on the advice here I made sure to do those look ups at the form level.

Thank you!!