Genealogy relationships

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 08:12
Joined
Jul 9, 2003
Messages
17,511
I have been looking at a database which is in essence a flat file type. It contains references to next of kin, father mother spouse etc. I think these items could be put into a new table called "tblNOK" (next of kin)

The basic information stored would be something along the lines of ID, Surname, Middle, First, Date of Birth.

Now this is the interesting bit, I thought I could link the next of kin to the principle person with a many to many table. And in that table I could also have an extra field that denotes the relationship. I wondered if anyone else had any views on this.

The other reason I am asking, is that I have seen some genealogy tables, and I realize there are some special codes used to denote links between relations. I am thinking I don't wish to reinvent the wheel!

Any comments would be greatly appreciated.
 
Actually, this is possible if you realize a little "trick" that Access supports.

When you want to define a self-referential relationship, you need two tables.

For your case: First, the PERSON table that lists each person and includes a PersonID that is unique (PK-eligible).

Second, the CONNECTION table that lists basically a few things:

tblConnection
FromID (LONG, FK, link back to tblPerson)
ToID (LONG, FK, link back to tblPerson)
Connection (Code, FK, link to a translation table - could be result of a drop-down fed by the translation table. Define relationships at will.)

But how do you do this? Easy - when you try to define the relationships, add the PERSON table to the relationship window TWICE. The first time, you tblPerson (or whatever you called it.) The second time, you get tblPerson(2).

Now link the FromID to the PersonID of tblPerson and link the ToID to the PersonID of tblPerson(2) - which is actually the SAME TABLE.

Then define the genealogy as you wish. For instance, FromID can select a person who is the grandfather of the person selected by ToID. So FromID and ToID have values and the Connection code selects "Grandfather."

But now, to make it symmetrical, you would also define "Grandchild" - and you could then take the contents of the FromID and ToID fields, reverse them, and select "Grandchild" - thus creating the "inverse" record to the first one.

This is a rather common issue because quite often, tables need to be self-referential. The "add it twice to the ... window" works great in such cases whether you are talking about queries or formal Access relationships.

Your connections (relationships) are now enumerated in the connections table through two (different) references to the same table. You can add possible relationships to your lookup table any time. Like

father/mother/parental unit
son/daughter/offspring unit
brother/sister/sibling unit
husband/wife/spousal unit
illegitimate offspring
ex-spouse
red-headed stepchild
mail carrier
secret lover
secret alter ego
identity before sex change

See? Any relationship you want...

To TRACE a relationship, though, will probably require some VBA code to do recursive translations by following From/To linkages to reach a terminal relationship.
 
Thanks for that Doc Man, I will give it a try.

Do you, or anyone else, know if there is a recognized list used in genealogy that defines these relationships?
 

Users who are viewing this thread

Back
Top Bottom