Append only new records to table (1 Viewer)

foshizzle

Registered User.
Local time
Today, 12:26
Joined
Nov 27, 2013
Messages
277
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;
 

Attachments

  • append-new-records-only.accdb
    792 KB · Views: 467

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:26
Joined
Feb 28, 2001
Messages
26,999
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.

Code:
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

Registered User.
Local time
Today, 12:26
Joined
Nov 27, 2013
Messages
277
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.

Code:
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!!!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:26
Joined
Feb 19, 2002
Messages
42,970
Even though we generally use autonumbers as the PK for tables, they have no meaning when it comes ot preventing duplicates. To prevent logical duplicates, you must add a unique multi-field index on the fields that define uniqueness. This will prevent append queries and forms from adding a duplicate. For the append query, you might be able to just ignore the dups but for a form, you should trap the duplicate error and provide a clear message to the user so they know what combination of fields constitutes uniuqeness.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:26
Joined
Feb 28, 2001
Messages
26,999
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:26
Joined
Feb 19, 2002
Messages
42,970
Adding a unique index to support business rules eliminates the problem. You can have the append query ignore duplicates. If you don't add the unique index, you can probably create duplicates using the form.
 

Users who are viewing this thread

Top Bottom