Append tables but weed out duplicates

[brandysoda]

I have been able to do most of this problem except for comparing two tables and deleting the duplicate records.

I import records from a text file into Table1. I have similiar a similar structured table T2. I have to append T1 to T2 but not the records already existing in T2(essentially i have to weed out duplicates from T1 before appending to T2). The two tables have autonumbers as their primary key. Can anyone shed light on the access basic logic to do this ?

 

[GHolden]

Assuming that you have a matching ID field in both tables (in this example CommonID).
The following SQL will insert into table T2 from table T1 where the CommonID in table T1 does not exist in T2 (ie. no duplicates).

INSERT INTO T2 (field1, field2..etc) SELECT field1, field2..etc FROM T1 WHERE T1.CommonID NOT IN (SELECT CommonID FROM T2)



There are two ways to write error-free programs; only the third one works.

 

[brandysoda]

Thanks GHolden. Worked Like a Charm !! Appreciate it.

 

[GHolden]

No problem...



There are two ways to write error-free programs; only the third one works.

 

[MichaelRed]

actually, you can just use an update query in most of these instances. Up date will append new records and 'update' fields of the existing records with values from the new ones - all at the same time. Read the docs (CAREFULLY) re -Update queries in (most) any useful SQL reference.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places