Solved

Remove Duplicate Access record, but keep orginal notes

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

776 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