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 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));