when importing data append only unique records

[Guest]

I need help. I am attempting to import external text data into a table. The
data that I am importing includes some duplicate records (same data as is
already in the access table). I want to import only the records that are not
already included in the access table. How do I do this?

Thank you!

 

[Tom van Stiphout]

On Sun, 4 Nov 2007 13:44:01 -0800, Zora

You could use a subquery to exclude data you already have (in this
case CustomerIDs):

insert into SomeTable
select * from AttachedTextData
where CustomerID not in (select CustomerID from SomeTable)

-Tom.

 

[Guest]

It depends on what your data looks like. If you've got a single field that
uniquely identifies a record, all you need to do is set that field as your
primary key in the table you're appending to, and set allow duplicates to no.
Whenever you append to that table, any duplicates will be rejected. This
would work on something like a customer master table, where you only have 1
entry per customer.

If your data has more than 1 field which defines a record, such as a sales
transaction table which might have many entries by the same customer on
different days, you'd need to do something more. You will need to create an
unmatched query, you can begin this process by using the wizard.. although it
will only allow a single linked field. You will need to edit the query after
finishing the wizard, simply use what the wizard created as an example and
copy it for each other field you need to link. Be sure to make new links
between your other fields as well as the entries in the criteria section.

Then change the query to an append, and you should be good to go.

 

[Panks]

On Sun, 4 Nov 2007 13:44:01 -0800, Zora
<(e-mail address removed)> wrote:

You could use a subquery to exclude data you already have (in this
case CustomerIDs):

insert into SomeTable
select * from AttachedTextData
where CustomerID not in (select CustomerID from SomeTable)

-Tom.



>I need help. I am attempting to import external text data into a table. The
>data that I am importing includes some duplicate records (same data as is
>already in the access table). I want to import only the records that are not
>already included in the access table. How do I do this?
>
>Thank you!

Thank you Tom! I have been trying to figure this out for hours and your post was the only one I could actually understand