Solved

Remove Duplicate Access record, but keep orginal notes

Posted on 2009-07-02
3
479 Views
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.
DELETE *

FROM PT_DS1

WHERE ID Not In (SELECT ID FROM PT_DS1 AS A 

WHERE Not Exists (SELECT True FROM PT_DS1 AS B 

WHERE A.[Order] = B.[Order] AND A.ID < B.ID));

Open in new window

0
Comment
Question by:wefarmer3
3 Comments
 
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.

0
 
LVL 45

Expert Comment

by:aikimark
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.
0
 
LVL 42

Accepted Solution

by:
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.






UPDATE PT_DS1 as A INNER JOIN PT_DS1 as B ON

A.OrderID = B.OrderID and A.ID <>  B.ID

SET A.comments = nz(B.comments + vbNewLine, "") & A.comments)

where

A.ID = (Select max(D.ID) from PT_DS1 as D where D.OrderID = A.OrderID)

and

B.ID = (Select min(C.ID) from PT_DS1 as C where C.OrderID = A.OrderID)
 

DELETE * 

FROM PT_DS1 as A

WHERE 

A.id = (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

0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 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

20 Experts available now in Live!

Get 1:1 Help Now