Unmatched Append help

[Opal]

I am running Access 2003 and I am trying to create an
unmatched append query. I import date to a temporary
table from excel once a week and I want to be able to
append any new data from my temporary table into my
working table. I went through the unmatched
query wizard and got the results I wanted. I then
changed the query to an append query and when I
attempt to run it get the following error:

Duplicate output destination

My SQL is as follows:


INSERT INTO EmpInfo ( EmpInfo.EmpNumber, EmpInfo.LastName,
EmpInfo.FirstName, EmpInfo.GLLink, EmpInfo.HireDate, EmpInfo.JobTitle,
EmpInfo.EmpNumber )
SELECT DISTINCT EmpInfoTemp.EmpNumber, EmpInfoTemp.LastName,
EmpInfoTemp.FirstName, EmpInfoTemp.GLLink, EmpInfoTemp.HireDate,
EmpInfoTemp.JobTitle, EmpInfo.EmpNumber
FROM EmpInfoTemp INNER JOIN EmpInfo ON EmpInfoTemp.EmpNumber =
EmpInfo.EmpNumber
WHERE (((EmpInfo.EmpNumber) Is Null));

Could someone help me get this query to work? Thank you!

 

[Marshall Barton]

I am running Access 2003 and I am trying to create an
unmatched append query. I import date to a temporary
table from excel once a week and I want to be able to
append any new data from my temporary table into my
working table. I went through the unmatched
query wizard and got the results I wanted. I then
changed the query to an append query and when I
attempt to run it get the following error:

Duplicate output destination

My SQL is as follows:


INSERT INTO EmpInfo ( EmpInfo.EmpNumber, EmpInfo.LastName,
EmpInfo.FirstName, EmpInfo.GLLink, EmpInfo.HireDate, EmpInfo.JobTitle,
EmpInfo.EmpNumber )
SELECT DISTINCT EmpInfoTemp.EmpNumber, EmpInfoTemp.LastName,
EmpInfoTemp.FirstName, EmpInfoTemp.GLLink, EmpInfoTemp.HireDate,
EmpInfoTemp.JobTitle, EmpInfo.EmpNumber
FROM EmpInfoTemp INNER JOIN EmpInfo ON EmpInfoTemp.EmpNumber =
EmpInfo.EmpNumber
WHERE (((EmpInfo.EmpNumber) Is Null));

Could someone help me get this query to work? Thank you!

You have an extra EmpInfo.EmpNumber at the end of both field
lists.

 

[Opal]

When I created the unmatched query, my SQL is:

SELECT EmpInfoTemp.EmpNumber, EmpInfoTemp.LastName,
EmpInfoTemp.FirstName, EmpInfoTemp.GLLink, EmpInfoTemp.HireDate,
EmpInfoTemp.JobTitle
FROM EmpInfoTemp LEFT JOIN EmpInfo ON EmpInfoTemp.EmpNumber =
EmpInfo.EmpNumber
WHERE (((EmpInfo.EmpNumber) Is Null));

And then when I take that same query and turn it into an
append query, it adds the second EmpNumber field....

Why is that? If I remove it, the query does not work at all...?

 

[Opal]

I just created two separate queries. Thanks.

 

[Marshall Barton]

When I created the unmatched query, my SQL is:

SELECT EmpInfoTemp.EmpNumber, EmpInfoTemp.LastName,
EmpInfoTemp.FirstName, EmpInfoTemp.GLLink, EmpInfoTemp.HireDate,
EmpInfoTemp.JobTitle
FROM EmpInfoTemp LEFT JOIN EmpInfo ON EmpInfoTemp.EmpNumber =
EmpInfo.EmpNumber
WHERE (((EmpInfo.EmpNumber) Is Null));

And then when I take that same query and turn it into an
append query, it adds the second EmpNumber field....

The field was added to the design grid because it is used in
the Where clause. The Show check box should have been
unchecked so it would not appear in the Select clause, but
what you got implies that Show was checked ??

Why is that? If I remove it, the query does not work at all...?

The field can not be removed, only the Show check box
unchecked.

This kind of confusion is a manifestion of not having a
detailed understanding of how the query design grid works.
Personally, I think it's way less confusing to use the query
designer to get most of the SQL written and then make the
final tweaks in SQL view where the rules are more clearly
defined.