Exclude Duplicates on Import

[Zonie32]

Hello. Is there a way to import a text file into an already existing database table without importing the duplicate records?

Each day I import a file into a master table, however some of the data are duplicates. The fields are always the same. The dups can be in the ClaimNumber field and the Patient Field. There could be more than one Patient with the same Claim number. So when I import if the Claim# and Patient are already existent in the database, I want to prevent the same claim number assigned to the same patient from being imported into the database.

 

[Norris68]

Pull it into a temporary table, duplicates and all. Then perform a SELECT DISTINCT query on the temporary table.

Unfortunately unless you write the import routine yourself you will not be able to exclude duplicates during the import phase. If you do write your own import routine, check whether the record exists before adding it to the table.

 

[BNPMike]

I haven't read your question in detail but a good way to reject duplicates is not to bother. If you declare an appropriate key in your table, Access will reject duplicates automatically for you.

 

[Zonie32]

BNPMike,
Thanks for the reply. I wanted to know would I set 2 primary keys, one for Claim# and the other on Patient?

ie, If a claim# of 001 is assigned to Bob, Mike and Joe and they are already in my database - then the next day I get a new file (which is a cumulative file) and that file shows Claim# 001 Bob, Mike, Joe and now Sally. I just want it to import Sally, because she is the new one. Would setting primary keys to both fields give me the results I am after?

 

[BNPMike]

You seem to be saying the key is Claim#+Patient. So set those two fields as the primary key. Look in help to see how to do that.

This will work as long as the data is like this

Claim Person
001 Bob
001 Mike
001 Joes
002 Bill
002 Janice