Remove Duplicate Access record, but keep orginal notes

Posted on 2009-07-02
Last Modified: 2012-06-27
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

Question by:wefarmer3
LVL 84
ID: 24766340
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.

LVL 45

Expert Comment

ID: 24766521
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.
LVL 42

Accepted Solution

dqmq earned 500 total points
ID: 24766658

>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


Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Close Print Preview button not active 12 35
Library not Registered 16 36
Running sum query 6 23
format date field on certain entries 8 22
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now