de-duplicating records whilst maintaining sub-records

[Hilary]

Having brought the contents of several different Access
databases under one umbrella, I now have the task of de-
duplicating whilst retaining the sub-records attached to
those duplicates. There are 14,000+ records with
probably around 5,000 duplicates, and some triplicates.
The sub-records contain important information that needs
to be attached to the one remaining main record. Can
anyone explain how this can be done, please? Thanking you
in anticipation. (I'm using Office XP Pro)

 

[John Nurick]

Hi Hilary,

Presumably each pair of "duplicate" records refer to the same person (or
whatever) but have different primary keys (otherwise there'd be no way
of telling which subrecord goes with which master record).

The algorithm is something like this:

For each pair,

1) decide which you want to delete. Suppose there's a pair whose primary
key values are 1345 and 9097 and you want to keep 9097.

2) run an update query on the child table that sets the foreign key
field to 9097 in all records where its current value is 1345.

3) run a delete query to delete record 1345 from the master table.

Repeat 5000 times.

This can be done in VBA code provided the rule(s) for identifying
duplicates and deciding which to keep can be expressed unambiguously in
terms of the data already in the database. The general idea is to run a
query that returns a recordset consisting of pairs of primary key values
from pairs of duplicates, and then to use code to iterate through this
recordset building and executing the appropriate update and delete
queries.

If you need help with this, I suggest you post in a VBA-related
newsgroup such as microsoft.public.access.modulesdaovba or
microsoft.public.access.formscoding.