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