Merge Duplicate records and Maintain relationships?

[jbh02]

I have a messed up database not mine I inherited it. It contains duplicate records in one table. I need to get rid of all but one of each record. The trouble is that all the duplicates have a separate primary key to related information in other tables. It should just be one to many instead of many (duplicates) to many related data.

I need to merge each duplicate into one primary key id in table, but also reassign the foreign key on the records in the other related tables to the single remaining primary key id in the first table.

If I just delete the duplicates I lose the related data in other tables that I need to keep and relate to the sole remaining primary key.

Any VBA code examples out there that could get me started?

[NTC]

you indeed have a mess....

the general strategy is this: make a new field in both tables named SuperKey - - then enter a unique value in both tables where the parent/child should exist; once that is done correctly then delete the duplicates in the parent table.

implementing the general strategy can be tricky...if there is a manageable amount of records you could do it by hand....if not you are going to need to do it via calculated fields or some particularly good sql - the explanation of which is probably beyond this forum...