Is there a way to specify only new records in an append query?

[sibbbra]

How should I have a result that shows only the records NOT already in the table I want to append to ?
The SQL code is as follows:
INSERT INTO Orders ( Product_ID, Customer_ID, Order_Date, quantity, Unit_Price_Sale, Amount_Sale )
SELECT DISTINCTROW tblorderproduct.Product_ID, tblorderproduct.Customer_ID, tblorderproduct.Date, tblorderproduct.Quantity_Sold, Products.PUnit_Price_Sale, ([Quantity_Sold])*[PUnit_Price_Sale] AS Am_Sale
FROM Customers INNER JOIN (Products INNER JOIN tblorderproduct ON Products.Product_ID = tblorderproduct.Product_ID) ON Customers.Customer_ID = tblorderproduct.Customer_ID;

Help would be appreciated

 

[theDBguy]

Maybe you could try using either the Not In() operator or the Not Exists clause. Just a thought...

 

[sibbbra]

thanx
but how to do that on the above example?

 

[Eugene-LS]

How should I have a result that shows only the records NOT already in the table I want to append to ?

I think it will take two more Queries (before that) - One for updating table "Orders" - Another to delete from "tblorderproduct" table
The second option is to greatly complicate your Query ...

 

[theDBguy]

thanx
but how to do that on the above example?

Not in front of a computer now, but in essence, the idea is something like this:

INSERT into Table1
SELECT FROM Table2
WHERE Field NOT IN Table2

 

[SHANEMAC51]

How should I have a result that shows only the records NOT already in the table I want to append to ?
The SQL code is as follows:
INSERT INTO Orders ( Product_ID, Customer_ID, Order_Date, quantity, Unit_Price_Sale, Amount_Sale )
SELECT DISTINCTROW tblorderproduct.Product_ID, tblorderproduct.Customer_ID, tblorderproduct.Date, tblorderproduct.Quantity_Sold, Products.PUnit_Price_Sale, ([Quantity_Sold])*[PUnit_Price_Sale] AS Am_Sale
FROM Customers INNER JOIN (Products INNER JOIN tblorderproduct ON Products.Product_ID = tblorderproduct.Product_ID) ON Customers.Customer_ID = tblorderproduct.Customer_ID;

I'm almost sure that if I buy beer 2 times a day, one of the records will be lost

 

[sibbbra]

I'm almost sure that if I buy beer 2 times a day, one of the records will be lost

hahahaha