Table structure, Child/Parent relationship for Family Tree Databas

Of all things, I'm creating a Family Tree database which uses many of the same terms as database such as Child/Parent etc....

My question is: What's the best way to set up the relation ship between the Real Children/Parents & Siblings. I am using MS ACCESS 2000.

Right now, here is a stripped down version of the pertinant tables:

PEOPLE TABLE:
Person ID - Primary Key
Last Name
First Name
Middle Name
Other personal information etc....

PARENTS TABLE:
Child ID - Primary Key - Linked to person in People Table
Mother ID - Linked to Person ID in People
Father ID - Linked to Person ID in People

The reason I chose to put the parents in a seperate table is so I can link the parents back to the People Table.... as far as I can tell, there is no way that I could link a "Mother" field in the People Table back to a Person ID in the same table.

Figuring out a way to link the siblings together without creating redundent data is also getting to be a challenge.

So far, I have tried to do this via the Parent's Table but don't know exactly how to filter the data. I have my "People" form which has a persons information as well as several subforms including one for Parents (which only shows the 2 parents from the PARENTS table) and a subform for SIBLINGS which lists all 3 fields from the PARENTS table.

I was hoping to be able to filter the Sibling's form by filtering out only the parents shown in the Parents subform for the person I have selected... if that makes any sense at all. That way I would get a list of all the Real Children of either parent shown in the Parents Subform.

So, is there a way I can show 2 subforms of the same sub-table, but have one of the subforms filter based on what is shown in the other subform?

BTW, I know there are Family Tree programs out there and I have tried some. The thing that I really hate about them is how they handle photos & documents. I am setting mine up so that I can have 1 photo linked to multiple people instead of having to copy a family photo into each person's file.

I guess the only other wishlist I have now is if anyone can point me in the direction of how to create a family tree report that has all the bubbles and lines... considering each time I do it will have different numbers of children etc....

Thanks for the help.

You've asked alot of questions and I can only offer you one suggestion that might help a few of the problems you describe.

You might consider another approach to your table design. Usually, one family has several People. One person may have several families. (Dad was married twice, Dad is member to GrandDad's family, etc) This is M:M so you need a junction table.

tblPeople
PeopleID (PK)
FirstName, etc...

tblFamily
FamilyID (PK)
FamilyName, etc.....

tblFamilyPeople
FamilyPeopleID (PK) 'rather than a multi-field pk.
FamilyID
PeopleID
RelationshipID

You may also want to add a Relationship table.

tlkpRelationship
RelationshipID
Relationship 'Father, Mother, Son, Daughter, etc.

I think you'll find it's easier to do the filters you can't do now.

hope this helps
--
rpw


"Mike" wrote:

Of all things, I'm creating a Family Tree database which uses many of the same terms as database such as Child/Parent etc....

My question is: What's the best way to set up the relation ship between the Real Children/Parents & Siblings. I am using MS ACCESS 2000.

Right now, here is a stripped down version of the pertinant tables:

PEOPLE TABLE:
Person ID - Primary Key
Last Name
First Name
Middle Name
Other personal information etc....

PARENTS TABLE:
Child ID - Primary Key - Linked to person in People Table
Mother ID - Linked to Person ID in People
Father ID - Linked to Person ID in People

The reason I chose to put the parents in a seperate table is so I can link the parents back to the People Table.... as far as I can tell, there is no way that I could link a "Mother" field in the People Table back to a Person ID in the same table.

Figuring out a way to link the siblings together without creating redundent data is also getting to be a challenge.

So far, I have tried to do this via the Parent's Table but don't know exactly how to filter the data. I have my "People" form which has a persons information as well as several subforms including one for Parents (which only shows the 2 parents from the PARENTS table) and a subform for SIBLINGS which lists all 3 fields from the PARENTS table.

I was hoping to be able to filter the Sibling's form by filtering out only the parents shown in the Parents subform for the person I have selected... if that makes any sense at all. That way I would get a list of all the Real Children of either parent shown in the Parents Subform.

So, is there a way I can show 2 subforms of the same sub-table, but have one of the subforms filter based on what is shown in the other subform?

BTW, I know there are Family Tree programs out there and I have tried some. The thing that I really hate about them is how they handle photos & documents. I am setting mine up so that I can have 1 photo linked to multiple people instead of having to copy a family photo into each person's file.

I guess the only other wishlist I have now is if anyone can point me in the direction of how to create a family tree report that has all the bubbles and lines... considering each time I do it will have different numbers of children etc....

Thanks for the help.

It's an interesting approach. I'm working on some queries to get the data I need in a new table. The reason I want to stay away from Families per-se is partly because of the divorce/remary issue and also that if there was ever a case where there wasn't a family unit (no marriage), I may end up having to create families where none existed.

Thanks,
Mike

"rpw" wrote:

You've asked alot of questions and I can only offer you one suggestion that might help a few of the problems you describe.

You might consider another approach to your table design. Usually, one family has several People. One person may have several families. (Dad was married twice, Dad is member to GrandDad's family, etc) This is M:M so you need a junction table.

tblPeople
PeopleID (PK)
FirstName, etc...

tblFamily
FamilyID (PK)
FamilyName, etc.....

tblFamilyPeople
FamilyPeopleID (PK) 'rather than a multi-field pk.
FamilyID
PeopleID
RelationshipID

You may also want to add a Relationship table.

tlkpRelationship
RelationshipID
Relationship 'Father, Mother, Son, Daughter, etc.

I think you'll find it's easier to do the filters you can't do now.

hope this helps
--
rpw


"Mike" wrote:

Of all things, I'm creating a Family Tree database which uses many of the same terms as database such as Child/Parent etc....

My question is: What's the best way to set up the relation ship between the Real Children/Parents & Siblings. I am using MS ACCESS 2000.

Right now, here is a stripped down version of the pertinant tables:

PEOPLE TABLE:
Person ID - Primary Key
Last Name
First Name
Middle Name
Other personal information etc....

PARENTS TABLE:
Child ID - Primary Key - Linked to person in People Table
Mother ID - Linked to Person ID in People
Father ID - Linked to Person ID in People

The reason I chose to put the parents in a seperate table is so I can link the parents back to the People Table.... as far as I can tell, there is no way that I could link a "Mother" field in the People Table back to a Person ID in the same table.

Figuring out a way to link the siblings together without creating redundent data is also getting to be a challenge.

So far, I have tried to do this via the Parent's Table but don't know exactly how to filter the data. I have my "People" form which has a persons information as well as several subforms including one for Parents (which only shows the 2 parents from the PARENTS table) and a subform for SIBLINGS which lists all 3 fields from the PARENTS table.

I was hoping to be able to filter the Sibling's form by filtering out only the parents shown in the Parents subform for the person I have selected... if that makes any sense at all. That way I would get a list of all the Real Children of either parent shown in the Parents Subform.

So, is there a way I can show 2 subforms of the same sub-table, but have one of the subforms filter based on what is shown in the other subform?

BTW, I know there are Family Tree programs out there and I have tried some. The thing that I really hate about them is how they handle photos & documents. I am setting mine up so that I can have 1 photo linked to multiple people instead of having to copy a family photo into each person's file.

I guess the only other wishlist I have now is if anyone can point me in the direction of how to create a family tree report that has all the bubbles and lines... considering each time I do it will have different numbers of children etc....

Thanks for the help.

Don't think of family in the traditional sense, but rather as a term used to classify a group to which people can belong. You can add FamilyType to the mix to distinguish between traditional family (mom married to dad, they have kids) or non-traditional (mom not married to mom#2, they have kids from prior marriages, artificial insemination, whatever).

In this case, Family is just a label for the M:M junction table.
--
rpw


"Mike" wrote:

It's an interesting approach. I'm working on some queries to get the data I need in a new table. The reason I want to stay away from Families per-se is partly because of the divorce/remary issue and also that if there was ever a case where there wasn't a family unit (no marriage), I may end up having to create families where none existed.

Thanks,
Mike

"rpw" wrote:

You've asked alot of questions and I can only offer you one suggestion that might help a few of the problems you describe.

You might consider another approach to your table design. Usually, one family has several People. One person may have several families. (Dad was married twice, Dad is member to GrandDad's family, etc) This is M:M so you need a junction table.

tblPeople
PeopleID (PK)
FirstName, etc...

tblFamily
FamilyID (PK)
FamilyName, etc.....

tblFamilyPeople
FamilyPeopleID (PK) 'rather than a multi-field pk.
FamilyID
PeopleID
RelationshipID

You may also want to add a Relationship table.

tlkpRelationship
RelationshipID
Relationship 'Father, Mother, Son, Daughter, etc.

I think you'll find it's easier to do the filters you can't do now.

hope this helps
--
rpw


"Mike" wrote:

Of all things, I'm creating a Family Tree database which uses many of the same terms as database such as Child/Parent etc....

My question is: What's the best way to set up the relation ship between the Real Children/Parents & Siblings. I am using MS ACCESS 2000.

Right now, here is a stripped down version of the pertinant tables:

PEOPLE TABLE:
Person ID - Primary Key
Last Name
First Name
Middle Name
Other personal information etc....

PARENTS TABLE:
Child ID - Primary Key - Linked to person in People Table
Mother ID - Linked to Person ID in People
Father ID - Linked to Person ID in People

The reason I chose to put the parents in a seperate table is so I can link the parents back to the People Table.... as far as I can tell, there is no way that I could link a "Mother" field in the People Table back to a Person ID in the same table.

Figuring out a way to link the siblings together without creating redundent data is also getting to be a challenge.

So far, I have tried to do this via the Parent's Table but don't know exactly how to filter the data. I have my "People" form which has a persons information as well as several subforms including one for Parents (which only shows the 2 parents from the PARENTS table) and a subform for SIBLINGS which lists all 3 fields from the PARENTS table.

I was hoping to be able to filter the Sibling's form by filtering out only the parents shown in the Parents subform for the person I have selected... if that makes any sense at all. That way I would get a list of all the Real Children of either parent shown in the Parents Subform.

So, is there a way I can show 2 subforms of the same sub-table, but have one of the subforms filter based on what is shown in the other subform?

BTW, I know there are Family Tree programs out there and I have tried some. The thing that I really hate about them is how they handle photos & documents. I am setting mine up so that I can have 1 photo linked to multiple people instead of having to copy a family photo into each person's file.

I guess the only other wishlist I have now is if anyone can point me in the direction of how to create a family tree report that has all the bubbles and lines... considering each time I do it will have different numbers of children etc....

Thanks for the help.

Hi Mike,

You didn't seem to like my other suggestion so I built one table, and one form and it displays you described in your post. More comments in-line below....


My question is: What's the best way to set up the relation ship between the Real Children/Parents & Siblings. I am using MS ACCESS 2000.

The reason I chose to put the parents in a seperate table is so I can link the parents back to the People Table.... as far as I can tell, there is no way that I could link a "Mother" field in the People Table back to a Person ID in the same table.


Yeah, you can do this if you really want to. Make your table like so:

tblPerson
PersonID
FirstName
LastName
Gender
FatherID
MotherID

Place the table three times into the relationships window. For the Father relationship, drag the PersonID field from tblPerson_1 to tblPerson-FatherID. For the Mother relationship, drag the PersonID field from tblPerson_2 to tblPerson-MotherID


Figuring out a way to link the siblings together without creating redundent data is also getting to be a challenge.

Why? A query that filters all records that have the same FatherId and MotherID as the current record will display the current record's siblings. Why do they have to be "linked"? Or you could query for matching FatherId or MotherID - then you'd get step-siblings too.


So far, I have tried to do this via the Parent's Table but don't know exactly how to filter the data. I have my "People" form which has a persons information as well as several subforms including one for Parents (which only shows the 2 parents from the PARENTS table) and a subform for SIBLINGS which lists all 3 fields from the PARENTS table.

I was hoping to be able to filter the Sibling's form by filtering out only the parents shown in the Parents subform for the person I have selected... if that makes any sense at all. That way I would get a list of all the Real Children of either parent shown in the Parents Subform.

So, is there a way I can show 2 subforms of the same sub-table, but have one of the subforms filter based on what is shown in the other subform?

I didn't use any subforms - just a list box on the main form with the following SQL for the row source:

SELECT tblPerson.PersonID, tblPerson.FirstName, tblPerson.LastName, tblPerson.FatherID, tblPerson.MotherID
FROM tblPerson
WHERE (((tblPerson.FatherID)=[Forms]![frmPerson]![FatherID]) AND ((tblPerson.MotherID)=[Forms]![frmPerson]![MotherID]))
ORDER BY tblPerson.FirstName;

And then put requery instructions in the current event of the form so everytime the record changed, the listbox would display the current siblings.

By the way, the controls FatherID and MotherID are combo boxes that have a row source to filter the current record out of the list.

Hope this helps you.....


BTW, I know there are Family Tree programs out there and I have tried some. The thing that I really hate about them is how they handle photos & documents. I am setting mine up so that I can have 1 photo linked to multiple people instead of having to copy a family photo into each person's file.

I guess the only other wishlist I have now is if anyone can point me in the direction of how to create a family tree report that has all the bubbles and lines... considering each time I do it will have different numbers of children etc....

Thanks for the help.

Oh, one more item to add. Another listbox that shows all children of the current PersonId. Here's the SQL for the second listbox:

SELECT tblPerson.PersonID, tblPerson.FirstName, tblPerson.LastName, IIf(Forms!frmPerson!Gender=-1,tblPerson!FatherID,tblPerson!MotherID) AS Expr1
FROM tblPerson
WHERE (((IIf([Forms]![frmPerson]![Gender]=-1,[tblPerson]![FatherID],[tblPerson]![MotherID]))=[Forms]![frmPerson]![PersonID]));

With this, it doesn't matter if the parent is father or mother, the children are listed.

So, now I''ve offered to you one table and one form. On the form you can view all of the details of the Person, all of the siblings to that person, and all of the children to that person. That's what you wanted, right?

If you want to add children or siblings to a person, that can be done with some VBA behind a couple of command buttons.

Here's a couple of other ideas for this setup: Have the listbox selection become the current record. Have a Double-Click event in the Father and Mother combos make the selection the current record. With this kind of event management, you'll be able to move up and down a family tree with no problem.

I hope you're still paying attention to this thread and that this approach is at least useful to you (or anybody reading it.) If it is, please post back - I'd appreciate knowing. (I'm not anywhere near an expert so this all took me a while to figure out.)


--
rpw


"rpw" wrote:

Hi Mike,

You didn't seem to like my other suggestion so I built one table, and one form and it displays you described in your post. More comments in-line below....


My question is: What's the best way to set up the relation ship between the Real Children/Parents & Siblings. I am using MS ACCESS 2000.

The reason I chose to put the parents in a seperate table is so I can link the parents back to the People Table.... as far as I can tell, there is no way that I could link a "Mother" field in the People Table back to a Person ID in the same table.


Yeah, you can do this if you really want to. Make your table like so:

tblPerson
PersonID
FirstName
LastName
Gender
FatherID
MotherID

Place the table three times into the relationships window. For the Father relationship, drag the PersonID field from tblPerson_1 to tblPerson-FatherID. For the Mother relationship, drag the PersonID field from tblPerson_2 to tblPerson-MotherID


Figuring out a way to link the siblings together without creating redundent data is also getting to be a challenge.

Why? A query that filters all records that have the same FatherId and MotherID as the current record will display the current record's siblings. Why do they have to be "linked"? Or you could query for matching FatherId or MotherID - then you'd get step-siblings too.


So far, I have tried to do this via the Parent's Table but don't know exactly how to filter the data. I have my "People" form which has a persons information as well as several subforms including one for Parents (which only shows the 2 parents from the PARENTS table) and a subform for SIBLINGS which lists all 3 fields from the PARENTS table.

I was hoping to be able to filter the Sibling's form by filtering out only the parents shown in the Parents subform for the person I have selected... if that makes any sense at all. That way I would get a list of all the Real Children of either parent shown in the Parents Subform.

So, is there a way I can show 2 subforms of the same sub-table, but have one of the subforms filter based on what is shown in the other subform?

I didn't use any subforms - just a list box on the main form with the following SQL for the row source:

SELECT tblPerson.PersonID, tblPerson.FirstName, tblPerson.LastName, tblPerson.FatherID, tblPerson.MotherID
FROM tblPerson
WHERE (((tblPerson.FatherID)=[Forms]![frmPerson]![FatherID]) AND ((tblPerson.MotherID)=[Forms]![frmPerson]![MotherID]))
ORDER BY tblPerson.FirstName;

And then put requery instructions in the current event of the form so everytime the record changed, the listbox would display the current siblings.

By the way, the controls FatherID and MotherID are combo boxes that have a row source to filter the current record out of the list.

Hope this helps you.....


BTW, I know there are Family Tree programs out there and I have tried some. The thing that I really hate about them is how they handle photos & documents. I am setting mine up so that I can have 1 photo linked to multiple people instead of having to copy a family photo into each person's file.

I guess the only other wishlist I have now is if anyone can point me in the direction of how to create a family tree report that has all the bubbles and lines... considering each time I do it will have different numbers of children etc....

Thanks for the help.