Append Query to only append new records, How to do this?

[d9pierce1]

Hi all - Good morning

I am trying to do an append query to only append new records (QryAttached)
I have watched several video and read a whole bunch of references yet i cannot get this to append only new records.


If I do it the way some vidoes show me i get an error message.
With that said, left table is the info I want to append to the right table. I added a # field in right table (TableID) as wouldnt work with the actual auto id.
The existing query actually works but creates duplicates if run over and over...
Any help with this would be so kind. I have three tables, (Purchase, Payment, and Deposit) that I need to append into this same table so i can create reports from one table
with deposits and withdrawals and such....In case you are curious

Design View



Sql

INSERT INTO tblCatagoryDetail ( [TableID], AccountID, CatagoryTypeID, CatagoryID, SubCatagoryID, Withdrawal, Travel, SubCatagoryName, TransactionID, TransDate )
SELECT DISTINCTROW tblCatagoryDetailPurchase.CatagoryDetailPurchaseID, tblCatagoryDetailPurchase.AccountID, tblCatagoryDetailPurchase.CatagoryTypeID, tblCatagoryDetailPurchase.CatagoryID, tblCatagoryDetailPurchase.SubCatagoryID, tblCatagoryDetailPurchase.Cost, tblCatagoryDetailPurchase.Travel, tblCatagoryDetailPurchase.SubCatagoryName, tblCatagoryDetailPurchase.TransactionID, tblCatagoryDetailPurchase.TransDate
FROM tblCatagoryDetailPurchase LEFT JOIN tblCatagoryDetail ON tblCatagoryDetailPurchase.[CatagoryDetailPurchaseID] = tblCatagoryDetail.TableID;

Thanks
Dave

[Micron]

One way is to create a select query that gives you only the records you want, then use that as a "table" in your append.
Another is to create a unique index on the table field(s) and run your query. You get a warning, not an error, about records that could not be appended. You can trap this in vba code or just ignore it if you're the only user (you know it basically means nothing of importance).

A potential issue is that your details table should have a field that holds the pk (primary key) from the left table (field in right table must be long integer number type), so ID 8 (for example) in left table is foreign key (fk, value = 8) that is stored in right table as many times as there are related records in right table. Maybe that's what "TableID" is?

[orange]

Dave,

Here's a sample that works. You could work from/with the structure.

INSERT INTO AnasttinNew ( Product, InPlaceYearStart, InPlaceYearEnd, YearOfData )
SELECT Anasttin.Product, Anasttin.InPlaceYearStart, Anasttin.InPlaceYearEnd, Anasttin.YearOfData
FROM Anasttin LEFT JOIN AnasttinNew ON Anasttin.ID = AnasttinNew.ID
WHERE (((AnasttinNew.ID) Is Null));

where:
-table to get the INSERTed records is AnasttinNew
-table to supply the "new" records is Anasttin
-the select list is the fields to be inserted
-(AnasttinNew.ID) Is Null means these records are not in the AnasttinNEW
--main point is the Left Join

Good luck,

[d9pierce1]

Hi Micron,
Thanks for input, yes, the TableID is the PK field for the left table and is a nunber, long int
The qry above does show me the records, just wont put in only new records. Looks like Orange has
A solution I will tryout,
Thanks
Dave

[d9pierce1]

Thanks Orange,
I will give this a shot. I beleive I can sort through it and make it work for me.
I eventlally will put this in the after insert on form so it works every time i add something new.\

Thanks again
Dave

[d9pierce1]

That worked very well orange,
Thanks
Will mark solved.
Thanks
Dave

[orange]

Dave
Glad it worked for you. Happy to help.


In my example one table was a copy of the other, the original had a few more records added.
So, I could use the autonumber field compare to identify the records that should be appended.