Solved

Remove Duplicate Access record, but keep orginal notes

Posted on 2009-07-02
3
483 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

679 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