Importing data into access and removing duplicate records

[EDGE99]

I have a transaction database that I have to update once a month. I receive a text file with all of the transaction data (2005 to current) so each month I will have the new records + all of the old records in the text file.

When I import the file I will get duplicate data as it contains all of the data from the previous import.

I played around with import features but there is nothing that will strip out the duplicate records. Is there a way to import the data and take care of taking out the duplicates?

 

[randy700]

Is your primary key included in the import? If not, how about the Find Duplicates Query Wizard?

Randy

 

[cranebill]

Is there a seq_no anywhere in there or a primary key?

If so, import into a seprate table, then transfer the records where the seq_no is greater than the max(seq_no) in the final table.

 

[randy700]

import into a seprate table, then transfer the records where the seq_no is greater

Why not just import the records where the sequence number is greater?


Randy

 

[cranebill]

Because he receives it in a text file format. So he has to get it in a table first prior, does he not?

 

[cranebill]

Or since it is a cumulative file, why not empty the table prior to loading.

 

[EDGE99]

All,

There is no primary key given in the file. I will play around with the Find duplicate wizard and also see if I can work with the sequence number. The seq_no might be the way to go here.

Thanks everyone I will let you know how it turns out or if I run into any issues. I appreciate all the input.

 

[randy700]

I think cranebill has hit upon the easiest solution. Since you get all of the previous data every time, just delete everything from the table first and it will be replaced along with the new data.


Randy

 

[EDGE99]

I think so that does sound like the best way to go. Do I just highlight everything and delete or is there a better way to clear the table?

Thanks for everything guys I really appreciate it.

 

[cranebill]

Before you delete everything make sure the records are contained in the file... at least back them up and check after loading.

As far as deleting easiest way is go to your query design tool, select table, then go to the query button at the top, select delete query

Select *, then run

 

[randy700]

DELETE * FROM [i]table[/i]

Randy

 

[cranebill]

Randy's way is from the SQL View in case you are unfamiliar....