Append Query question!

[Guest]

Is it possible to do append query to unique records. As in If one forgets
and presses the append query more than once, there is a possiblility that the
same records would be appended more that once. What I would like to do is
one runs the append query, don't append the same records or have duplicate
records in the appended table...

 

[Brendan Reynolds]

Here's an example that uses two copies of the Orders table from the
Northwind database ...

INSERT INTO Orders2 ( OrderID, CustomerID, EmployeeID, OrderDate,
RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
ShipCity, ShipRegion, ShipPostalCode, ShipCountry )
SELECT Orders1.OrderID, Orders1.CustomerID, Orders1.EmployeeID,
Orders1.OrderDate, Orders1.RequiredDate, Orders1.ShippedDate,
Orders1.ShipVia, Orders1.Freight, Orders1.ShipName, Orders1.ShipAddress,
Orders1.ShipCity, Orders1.ShipRegion, Orders1.ShipPostalCode,
Orders1.ShipCountry
FROM Orders1 WHERE Orders1.OrderID NOT IN (SELECT Orders2.OrderID FROM
Orders2);

 

[Guest]

(It does depend on Criteria) Exzample-- you can append all the records in a
zip code or all the records with a M as the first letter in the last name.
but should it append in random? no I havent had that experence.
If their are records you dont want use criteria field to append only the
records you do want.

 

[Guest]

I put that into the criteria area of my append query and got syntax errors
that I could not correct as I am new to this. Is there a misplaced
paranthesis somewhere?

 

[Brendan Reynolds]

What exactly did you put in the criteria area of your query? In the example
I posted, the criteria is everything between 'WHERE' and the semi-colon,
exclusive, that is to say not including the word 'WHERE' or the semi-colon.
And of course you would need to change the table and field names to your
actual table and field names.

 

[Brendan Reynolds]

Correction - if you were typing that into the criteria row in query design
view, you would also leave out the name of the column, so the criteria would
look like ...

NOT IN (SELECT Orders2.OrderID FROM Orders2)

.... not forgetting to replace 'Orders2' with the name of your target table
(the table to which you are appending) and 'OrderID' with the name of a
suitable column (the primary key column or another column with a unique
index).