Delete / Combine Duplicates in Access Table

[kschmidt]

The good ole delete duplicates....

So I have an access table that contains duplicates resulting from either mutiple owners of a property or an order report of the owners (see attached excel for the structure of my table). I have already used this code to clean exact duplicate records, but that doesn't catch everything. I'd like to accoplish two more tasks:

1) Where the DISP_NUM and DISP_DATE are the same, I would like to combine the contents of the OWNER field. For example, I would like

DISP_NUM | OWNER | DISP_DATE
GRL34534 | Kimble, David | 21/10/2008
GRL34534 | Kimble, Sharon | 21/10/2008

to become

DISP_NUM | OWNER | DISP_DATE
GRL34534 | Kimble, David; Kimble Sharon | 21/10/2008

2) Where DISP_NUM and OWNER are the same, I would like to delete the older record based on DISP_DATE

I would like to accomplish this with a vb script if possible.

[Ecniv]

#2

open a recordset, sorted on your key fields
Loop from top to bottom, any dups, delete is the same as the previous.



#1
dunno - depends on your structure. If you have a unique field as the relation al one then you could create a merged record, erase the two originals and reset the link to the new record.

Otherwise, similar to #1, open a sorted recordset, if the record is the same, drop the merged record into an array, storing the key unique identifier. Then loop through all changes held in the array updating records.
Then erase the no longer required ones...