Combining People in Same Family

You could make a new table for Families and make the primary field in that table an Autonumber

Not sure if I read this right but you could just have a contacts table, each contact is in there and a relationship field (reference another record) where you would link to the other member(s) in the table. OR have a contact type field, this would be individual or family as a single select dropdown. Then you have all the individual contacts and the master family name entry and using another reference field, link both individuals to the family name. Then you can sort by family name and see all the contacts.

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:

1. Create an autonumber field in your [Individuals] table called {A Nbr}.
2. Create a number field in your [Individuals] table called {Family Override}.
3. Create a formula field in your [Individuals] table called {Family Nbr} with the formula IF({Family Override},{Family Override},{A Nbr}).
4. Create a linked-record field in your [Individuals] table called {Family} that points to a new table called [Family].
5. 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.
6. 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.
7. 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:

Thank you very much everyone for your suggestions and time! I’m interested in recruiting children for research studies and that’s why I’m creating this database.

It is quite confusing to figure out if individual children or families should be the higher order table. I ended up creating a table that is called Families (includes parent names and contact info) that I linked to a table called Children (includes demographics of children), and both families and children have their own unique number ID.

I’m creating a database from scratch, so I think this is the easiest route. When I get a new family, I can enter all their data into the Family Table and assign them an autonumber as their Family ID. Then I linked this to another table called Children, which assigns an autonumber to each newly entered child as a Child ID and contains all the kids relevant info (DOB, sex, etc.). I will typically be looking at the Children table, but lookup is a life saver. I’m able to get any relevant entries I need into the the Children Table from the Family Table using lookup (e.g., Family ID, phone number). If others end up having different suggestions, feel free to keep posting on this thread as I’m sure lots of people have run into this problem.

Yes, if you’re building the table from scratch and you know siblings prior to data entry, then doing it in a traditional manner — [Families] and [Individuals] cross-linked — is preferable. Your initial mention of ‘rows for ID 1001 and 1002’ led me to assume you had a table of > 1000 individuals already, which would make retrofitting a [Families] table a pain in the base. :winking_face: