Is there a way to specify only new records in an append query? (1 Viewer)

sibbbra

Member
Local time
Today, 14:02
Joined
Feb 11, 2022
Messages
78
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

I’m here to help
Staff member
Local time
Today, 07:02
Joined
Oct 29, 2018
Messages
21,357
Maybe you could try using either the Not In() operator or the Not Exists clause. Just a thought...
 

sibbbra

Member
Local time
Today, 14:02
Joined
Feb 11, 2022
Messages
78
thanx
but how to do that on the above example?
 

Eugene-LS

Registered User.
Local time
Today, 17:02
Joined
Dec 7, 2018
Messages
481
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

I’m here to help
Staff member
Local time
Today, 07:02
Joined
Oct 29, 2018
Messages
21,357
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

Active member
Local time
Today, 17:02
Joined
Jan 28, 2022
Messages
310
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:02
Joined
Feb 19, 2002
Messages
42,970
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.
 

Users who are viewing this thread

Top Bottom