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

 

[Pat Hartman]

To avoid duplicates, you should always define unique indexes. Access will ignore the rows that already exist. Without some unique identifier, you can't tell what has already been added anyway.

If you do the upload only once per day, you can use criteria that selects only the most recent date.