Append only new records to table

[foshizzle]

I'm running an APPEND query and trying to only insert records that do not yet exist. This query appends records from two different tables as follows:

tblAirlineTransactions
TransactionDate, AirlineCompany, IssueType, GallonsReceived

tblGainLoss
TransactionDate, AirlineCompany, IssueType, GainLoss

The tblGainLoss.GainLoss field should append to tblAirlineTransactions.GallonsReceived. I can make all this happen but I'm unable to figure out how to only append new records into tblAirlineTransactions. When I try entering a WHERE clause, the results for the query aren't the same so I've left out the WHERE in the sample. Can someone point me in the right direction?

Here is my code:
INSERT INTO tblAirlineTransactions ( TransactionDate, AirlineCompanyLookup, IssueTypeLookup, GallonsReceived ) SELECT SQ1.TransactionDate, SQ1.AirlineCompanyLookup, SQ1.IssueTypeLookup, SQ1.GainLoss FROM (SELECT tblGainLoss.TransactionDate, tblGainLoss.AirlineCompanyLookup, 13 AS IssueTypeLookup, tblGainLoss.GainLoss FROM tblGainLoss WHERE (((tblGainLoss.TransactionDate)=#4/30/2021#))) AS SQ1 LEFT JOIN tblAirlineTransactions ON (SQ1.[TransactionDate] = tblAirlineTransactions.[TransactionDate]) AND (SQ1.[AirlineCompanyLookup] = tblAirlineTransactions.[AirlineCompanyLookup]) GROUP BY SQ1.TransactionDate, SQ1.AirlineCompanyLookup, SQ1.IssueTypeLookup, SQ1.GainLoss;

 

[The_Doc_Man]

Not sure about which key is your primary, however your request is not that hard. I'll show you the simplest form of it to give you the idea.

INSERT INTO TableC (Fld1PK, Fld2, Fld3, Fld4 )
SELECT A.Fld1PK, A.Fld2, A.Fld3, A.Fld4
FROM TableA AS A WHERE (other "where" elements go here) AND A.Fld1PK NOT IN ( SELECT C.Fld1PK FROM TableC AS C ) ;

The basic idea is that the NOT IN clause says to look for matching records in the subordinate clause (the inner SELECT) and disallow the use of that record if the PK is already there.

This is an example of a subordinate clause, which is what you would look up if you wanted to do more reading on the subject.

 

[foshizzle]

Not sure about which key is your primary, however your request is not that hard. I'll show you the simplest form of it to give you the idea.

INSERT INTO TableC (Fld1PK, Fld2, Fld3, Fld4 )
SELECT A.Fld1PK, A.Fld2, A.Fld3, A.Fld4
FROM TableA AS A WHERE (other "where" elements go here) AND A.Fld1PK NOT IN ( SELECT C.Fld1PK FROM TableC AS C ) ;

The basic idea is that the NOT IN clause says to look for matching records in the subordinate clause (the inner SELECT) and disallow the use of that record if the PK is already there.

This is an example of a subordinate clause, which is what you would look up if you wanted to do more reading on the subject.

Hi Doc, these two tables obviously have two different PKs. In this case, the PK in tableA would never be in tableC. I was confused of why I should bother with the PK fields. At any rate, I got it working with your advice using this:

INSERT INTO tblAirlineTransactions ( TransactionID, TransactionDate, AirlineCompanyLookup, IssueTypeLookup, GallonsReceived ) SELECT A.GainLossID, A.TransactionDate, A.AirlineCompanyLookup, A.IssueTypeLookup, A.GainLoss FROM tblGainLoss AS A WHERE (((A.TransactionDate)=#4/30/2021#)) GROUP BY A.GainLossID, A.TransactionDate, A.AirlineCompanyLookup, A.IssueTypeLookup, A.GainLoss HAVING (((A.GainLossID) Not In (SELECT C.TransactionID FROM tblAirlineTransactions AS C )));

Thank you!!!

 

[The_Doc_Man]

Hi Doc, these two tables obviously have two different PKs. In this case, the PK in tableA would never be in tableC. I was confused of why I should bother with the PK fields. At any rate, I got it working with your advice using this:

INSERT INTO tblAirlineTransactions ( TransactionID, TransactionDate, AirlineCompanyLookup, IssueTypeLookup, GallonsReceived ) SELECT A.GainLossID, A.TransactionDate, A.AirlineCompanyLookup, A.IssueTypeLookup, A.GainLoss FROM tblGainLoss AS A WHERE (((A.TransactionDate)=#4/30/2021#)) GROUP BY A.GainLossID, A.TransactionDate, A.AirlineCompanyLookup, A.IssueTypeLookup, A.GainLoss HAVING (((A.GainLossID) Not In (SELECT C.TransactionID FROM tblAirlineTransactions AS C )));

Thank you!!!

You are welcome. As I commented, I wasn't sure which field was the PK, and that clearly makes a difference in your ability to identify which records in the "new" table are already in the "old" table. You got the message, though, because a sub-query was clearly what you needed.