Solved: Family Tree / Hierarchy Database Design

[tammyl]

Hi,

Hope somebody can point me in the right direction or provide some advice.

I wish to create a contacts database that also links individuals together to create families. On my form I wish to have a 'Family/Group' section which has Family name, address, home numbers - all those details common - so I only have to change these details once.

Then on the same form, I wish to list & update details for individuals that are linked to the Family Name. Their details would be birthdate, children, parents, work info, notes, etc.

And so the hierarchy continues.

I've read a few articles on access self-joins but i'm unsure if I put all the data in one table how I get to have distinct records when there are multiple groups that will have the same 'Family Name' and how to link them to the 'Persons' correctly.

So far my data is a small table with basic person info only with a primary key field of ContactId (Person-Autonumber) so the database doesn't allow duplicate people. I haven't put in the Family name section as yet with addresses, etc.

Example:

Family name - Smith
in household Person1 - John
in household Person2 - Mary
in household Child1 - Bob
at different household Child2 - Betty
at different household Child3 - Fred

Family name - Smith
in household Person1 - Fred (son of John & Mary)
in household Person2 - Jane

When I get this sorted (fingers crossed) and expand my access knowledge further, I wish to be able to double-click on the children and then their record appear as the main record in my form....

Any help or directions really appreciated, as I've got all this info at my fingertips and can't wait to get it setup looking 'specky' ready for a phone list report & family tree report......and bin my paper clutter.

Cheers
Tammyl :help

[OBP]

Tammyl, you need multiple Tables to do this properly.
I would start with the Main Contact as the main table.
Each record in that table should have an Autonumber ID field.
If you want the Spouse in the same Table that is OK. It will need an AddressID field type number - Long Integer.
I would have a seperate Address Table also with an Autonumber ID field, the Contact table AddressID would hold that number to link the contact with the Address.
Then you need a Children Subtable which has a ParentID field Type Number - Long Integer which has the Main Table ID number in it to link the 2 tables.
Some people will appear in both tables as they will be children and parents.
Your children's Table will also need an Address ID field to link it to the Addess.

[tammyl]

Thankyou. I shall setup the tables as suggested.

Cheers

[OBP]

tammy, if you need any further help with the design please let me know.