Re: Deleting duplicate parents and grouping children to single parent

It's not exactly simple, Jonathan.

Firstly, you need a way to identify "duplicate" parents. If the data is identical, you can use the Find Duplicates query wizard to find those records for you. But chances are that the data is not identical, e.g.:
title could be Ms or Mrs
Address could be slightly different:
7 Fourth St
7 Fourth Street
7 Forth St
7 Forth Way
And so on. Typically only a human eye can make the decision.

Once you have identified duplicates, the next task it to decide which one of the 2 to keep, to reassign the children of the other to this one, and then delete the other. To automate that will require some coding experience. Essentially you will execute an Update query to change the ParentID of the affected children, and then exeute a Delete query to kill off the undesirable parent record. Ideally you will exeucte that in a transaction (particularly if there are other related tables for the parents.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jonathan" <Jonathan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F2733CDB-57EE-4DA4-A996-EC178485A8CD@xxxxxxxxxxxxxxxx
So here is my question. I'm building a rather simple db consisting of a
parent child and child table in a one-to-many relationship (based on the
Parent's ID and the Child's "ParentID" field). For the intended workflow,data
will be regularly entered by importing or cutting and pasting records from an
Excel work***. The work*** rows will contain both parent data and child
data and I've set up a query so this can proceed smoothly. But the result is
that much of the parent data is duplicated. Is there a way for me to automate
the process whereby, after import, duplicate parent records are identified
(say, by one or two matching fields, perhaps more) and the children of the
duplicate parent records can have their ParentID field reassigned to the
first of the duplicate parent records?

This is a problem I've never dealt with before and intrigues me. My users
all have very little experience so it needs to be simple (one or two clicks
perhaps?)

Let me know if I need to explain further.

AND very importantly, an unfortunately, this has to be implemented in Access
2000, though in 6 months I might be upgrading.

Thanks much,

Jonathan

.

Loading