Duplicate Values

[Accessosaurusrex]

I have a handheld barcode scanner which sends data to a CSV file that is then imported to an Access 2003 database. From the import table, the data is appended to a user table with primary keys set to prevent duplicate data. I use 5 fields to make a primary key. The problem is that we update one field of the five to indicate a process. This changes the data in the user database and allows the original record to be re-imported as a duplicate because that particular field does not match anymore.

I am looking at how best to address this. I do have an autonumber field that I could use to prevent duplicate entries from being imported, but I am not aware of the ability to do 2 successive key check operations on an append query.

My next thought was to append with the duplicates and then do a delete query. I need to keep the record with added data, not the one that will be reimported. Not sure how I could do that query.

Any suggestions would be greatly appreciated.

 

[Jeanette Cunningham]

Use one additional field to indicate a process instead of changing the value of a field that is part of the index - perhaps a long integer or a yes/no for the additional field.

You can choose to only append those records where the process has a specific value - append only those that are processed for example or the other way round, depending on your needs.

 

[Accessosaurusrex]

Thank you very much for the advice. I had thought of that but to be honest was just being lazy as there are about 14 reports off this table and I will need to go change them all in order to do that.

Anyway, you are correct. That is the right way to go. Getting my report editing fingers all loosened up now!

Thanks again!