find unmatched and append in one query? 1

[Eupher]

Ack! My SQL is weak...

Two tables, TableA and TableB, identical structure. The help files for an append query say to first create a query that contains the records you want to append - in this case, a "find unmatched records" query (TableB contains records I want to append to TableA). My question: is there a way to incorporate the whole operation into a single query, i.e. append TableA with unmatched records from TableB - without the intermediate step of the "find unmatched records" query?

Thanks!

Ken S.

 

[Hakala]

I was able to transform the Unmatched Query into an Append Query and run it only by not inserting the field I matched by.

So I have two tables: tblRequests and tblRequestsRemoved and I'm using two fields from each: ID and oldID

I created a Find Unmatched query based on the field OldID which is in both tables but was only valid early on, so the tblRequestsRemoved only has data there. This query only had the fields ID and oldID, and searched based on oldID being empty in tblRequests

Then I converted the new Find Unmatched Query to an Append Query

I got errors based on circular reference and duplicate output, but this is the SQL from the final query that works:
INSERT INTO tblRequests ( ID, OldID )
SELECT tblRequestsRemoved.ID, tblRequests.OldID
FROM tblRequestsRemoved LEFT JOIN tblRequests ON tblRequestsRemoved.OldID = tblRequests.OldID
WHERE (((tblRequests.OldID) Is Null));

Hope this helps!

 

[Eupher]

Thanks, Hakala, that works. A star for you.

Ken S.