Append only unique records

tobabygu · #1 (**permalink**) 02-10-12, 16:41

Hello.

I was hoping some could show me how I could only append records that are not already in the table. Just the unique records. I have field named SeqOrder that counts number of records (autonumber) but outside of this field if the rest already exist I don't want the record to append to the table. Records are being appended one at a time. I look online for the solution and it seemed like I would have to do a inner join or something but I didn't quite understand it. Here is my sql for my append query.

Code:

 INSERT INTO Taxable_Facility ( PropertyID, TaxDue, DueDate, PaidDate )
SELECT [Table1].PropertyID, [Table1].TaxDue, [Table1].DueDate, [Table1].PaidDate
FROM [Table1]
WHERE ((([Table1].PropertyID)=[Forms]![Taxable_Facility_Form]![PropertyID]));

I've tried making it select distinct but that didn't work.

Thanks in advance.

dave0810471 · #2 (**permalink**) 02-11-12, 09:59

Hi there,

I would use one of two methods. You can either use SQL that will look for matches on all fields from both tables but where the destination table is missing the ID (auto number) field then append the results of this

OR

You could ensure that each record has a unique identifier (other than the auto number field) and simply check for a match on this field rather than on each field.

Attached is a DB that will demonstrate the first solution. Any unique jobs found in Jobs2 will be appended to Jobs1 by running the Compare append query. This can be adapted to suit your needs. You'll notice in the compare query every field is joined to check for matches whereas if you were to use the second solution you would only have to join on one field (the unique identifier other than auto number) to see if there was a match.

Hope this helps,

Dave