Remove Duplicate Access record, but keep orginal notes

I have a table that has duplicate records.  I cannot avoid getting duplicate records due to the type of data that is being entered.  For instance I may have a record for order A, that asked to be shipped on 7/10, the cus may then change the requested ship date to 7/11.  This would create a duplicate record for order A, but just different shipping dates.  I need to keep the record with the latest ship date, and copy the comments from the original file.  I am using the query statement below to keep the last entry but don't know how to copy the comments from the first instance of order A to that last instance of order A.

I would prefer VB code to do everything, but will use a query or anything that might work.  I will have to run the duplicate check every day on 4 different tables that can hold up to 10000 records each.  There are multiple columns, but the three major ones are [Order], [Received], [Comments].  I need to find duplicates in the [Order] column.  Keep the record with the latest [Received] date, and copy the data in the [Comments] in the first instance to the [Comments] in the last instance then delete the first instance.
WHERE A.[Order] = B.[Order] AND A.ID < B.ID));

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Is this a program you designed? If so, it would seem that you would simply update the Received instead of creating a new record - that is, in the User Interface, enforce this policy, not at the data level.

Why create a new record when it is so easy to change the existing record?!?
(concurrence with LSM thoughts)

How about a separate table for comments?  You could just change the linking number from the original to the new record.

You would run an Update query on a joined view of the table joined to itself on the ORDER column, limiting rows to those joined records where the ID (autonumber) values are not equal.

Note: the update query must run before your Delete query.

You will have a more efficient process if you perform a logical deletion of superceded rows and then a true deletion.  Add a boolean column to the table to indicate the row has been superceded.  Alternatively, you might include a supercedes column that allows you to back-track customer-related change activity.  However, following the LSM advice would be the best approach from both a performance and maintenance criteria.

Note: You might also consider a change log table that will retain the old date values of the changed rows.

>I need to keep the record with the latest ship date

Really? That's not what your delete SQL does.  Your SQL deletes all but the most current record regardless of the ship date.  That's probably what you meant to do--just wanted to be sure.

One issue arisies is t hat you may have more than two records for the same order.   Your DELETE removes all of the older ones, so then you need to pickup comments from all of them?

The attached code has been adapted to update the newest record from the oldest and then delete it.  So, I'd suggest putting it inside a loop that runs until there is a single record for the order.  Also, it should be inside a transaction.

Finally, I agree with LMS: if you don't care about the history of changes, then change the application to do an update instead of an insert.  If you do care about the history, then don't delete it.  You can create a view that returns only the most current record and another that returns the comment history.

A.OrderID = B.OrderID and A.ID <>  B.ID
SET A.comments = nz(B.comments + vbNewLine, "") & A.comments)
A.ID = (Select max(D.ID) from PT_DS1 as D where D.OrderID = A.OrderID)
B.ID = (Select min(C.ID) from PT_DS1 as C where C.OrderID = A.OrderID)
WHERE = (Select min(B.ID) from PT_DS1 as B
    where B.OrderID = A.OrderID
    group by B.OrderID
    having count(*) > 1) 

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.