Take a look at the section on ‘Merging True Duplicates’ in the latest version of my Data Deduplication routines. It uses a self-join to merge duplicates, with one record arbitrarily chosen as a master and all the rest as children; you could modify that slightly to roll together siblings under one of the records.
If you truly need a standalone record for each family, so that you’d have either a one-to-one or one-to-many link between family and family member(s), @Kamille_Parks is on the right track — but the easiest way is to do it a little bassackwardly:
- Create an autonumber field in your
[Individuals] table called {A Nbr}.
- Create a number field in your
[Individuals] table called {Family Override}.
- Create a formula field in your
[Individuals] table called {Family Nbr} with the formula IF({Family Override},{Family Override},{A Nbr}).
- Create a linked-record field in your
[Individuals] table called {Family} that points to a new table called [Family].
- Create a lookup field in
[Family] that follows the link back to [Individuals] and looks up whatever field you plan to use to identify families. Presumably, {Last Name} alone won’t do it, but I don’t know enough about your data to make any suggestions.
- After populating your
[Individuals] table, click the header to {Family Nbr} to mark the column. Press Ctrl-C to copy the entire column of values.
- Click the header to
{Family} to mark the column. Press Ctrl-V to paste the entire column of values.
Now you’ll have one-to-one linked records between [Family] and [Individuals].
As you go through and group siblings, you do so by entering the {Family Nbr} for Sibling #1 into {Family Override} of Siblings #2 and beyond. (How you determine which sibling is #1 is unimportant — well, unless you’re one of the siblings, I guess. :winking_face: ) Once you have all of the siblings grouped in such a way, repeat Steps 6 and 7, above, to update the base.
This method keeps you from having to keep switching back and forth between bases to make manual links, as all your decision-making takes place in the [Individuals] table, where the information is located. You can add a rollup field to [Individuals] that will pull together a list of siblings when applicable. It also easily supports a working base — that is, one where you are continually adding [Individuals] — as the copy-and-paste steps will create or update [Family] records as necessary.
There are two potential downsides. The first is that the primary field for [Family] is a numeric value, not a family name — but, then, with so many siblings not necessarily having the same family name, this might not be that much of an issue. The second is that you will eventually have a number of entries in [Family] that aren’t linked to an individual. These are safe to ignore, but if it offends your sense of order, you can occasionally filter [Family] for records where the link to [Individuals] is empty and delete all the records thus identified.
As an aside, I should mention this works equally well for bases, um, based on imported records — but it does so in a way somewhat different from usual practices. Typically, when you create a base, you import records into what is essentially the root level of your data structure — in this case, the [Family] table — and then built outwards into branches of the data hierarchy. With the method I just outlined, you’re actually importing one level higher than the root, into [Individuals], and then using the data from that table to create a lower-level table to hold family data. That might make perfect sense to you, but it confused the heck out of me the first time I did it in a client base. :winking_face:
