Unique Data only import

[Thwarted]

Hello,
I posted something similar a few days ago - after trying some things i have found i am no further ahead. I am hoping someone out there has some fabtabulous advise for me

I have an automated import that imports data from a .csv file. Once it has been imported there is a batch file that then renames and moves that particular text file - so that it cannot be imported accidentally more than once into access. The problem is - the csv files come from a download off a handheld device - if the device is not cleared and they download again the process starts over so we can get the same data in Access accidently. I need to prevent that.

What happens now is - all the data is imported to a temp table and then an append query is run - the append query contains some functions that fill in missing data so basically the data gets cleaned up.

Is there an easy way to add an If statement before the append query is run: If fields 1,2,& 3 in tbl_Temp match fields 1,2,&3 anywhere within the last 100 records of tbl_Permanent then do not append?
Does that make sense??
Does anyone know how i can do this?
Thanks a million in advance.

T

 

[KenReay]

Hi

Your comment:

"Is there an easy way to add an If statement before the append query is run: If fields 1,2,& 3 in tbl_Temp match fields 1,2,&3 anywhere within the last 100 records of tbl_Permanent then do not append"

Implies that fields 1,2,3 form a unique key?, if this is so, simply amend the table to define these fields as a unique key, then Access will not append duplicate records, they will be rejected. Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[Thwarted]

Ken,
Thanks for your response -
What if they dont form a unique key? I have an Autonumber primary key set. They need this unique # to search for certain entries etc. so i dont really want to remove it.

 

[KenReay]

Hi

You do not have to remove it, you can have an autonumber PK AND declate a (compound) Index and decalre it as duplicates not allowed Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[MichaelRed]

W/o the autonumber stuuuuuuuufffffff,

Change the append query to an update query.









I'm sure there will be howls re this but it DOES work. For the non--believers, try it on a small sample and ponder the nature of UPDATE.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over