Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jun 2006
    Posts
    3

    Red face Unanswered: Many-to-Many Relationship conundrum

    I'm trying to build a simple(!) database that can be used to track members of family from the perspective of any child in any one family.

    I have the following tables:
    - Child(childID, childFirstName, childLastname), and
    - Adult (adultID, adultFirstName, adultLastName, relationship(to child))

    As this is a many-to-many relationship (i.e. any child can have more than adult relation; any adult can have more than one child) I have created a third link table called Relations(AdultID, childID) and defined one-to-many relationships from the Child and Adult tables .

    I have created an entry form for children that includes a sub-form so when entering details of a child, I can also enter the details of their parents, aunts uncles etc. So far so good....

    However, I'm having difficulty getting to grips with the following:

    1. I don't seem to be able to define the relationship between children and adults in the child entry form. The only way I can link children and adults is to open the relationship table and enter childID and adultID pairs. Is there any way to do this "automatically" when completing the child entry form?

    2. How on earth do I deal with sibbings??

    Any help / advice would be most humbly received!

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by sharrisonUK
    2. How on earth do I deal with sibbings??
    ...and what about half-siblings & step-siblings?

    My thought is your table set up looks okay. I hope you're pretty good
    with nested queries.
    Last edited by RedNeckGeek; 06-23-06 at 15:46.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    what happens when a child has a child?

    a person is a person (ignore child-ness & parent-ness since any person might be both, or only child, or only parent if adoptive parents are unknown to your database, or neither if childless/adopted)

    so:
    tblPerson(all attributes thereof except child-ness/parent-ness )

    each person has N-parents (or N-children if you prefer) - each of which is just a person in tblPerson.

    tblRelation(PK, IDtblPersonAsChild, IDtblPersonAsParent)

    ...but there is still RNG's concern about walking the tree to find great-grandparents or great-grandchildren or second-cousins of X.

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Fine tune on Izy's Relation table ...

    Change it to something like:

    Relation
    -------
    RelationID
    PersonID
    RelatedToID
    RelationshipType

    Where RelationshipType can be Siblings, Parent, Child Of, Grandparent Of, etc ... Expanding out to the halfs, steps, and such ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    The model sounds a lot like a multi-level bill of materials, which is actually a fairly complicated model to work with.

    Generally, I have two tables and i will put it into terms of family tree:

    Table 1 has each person, Table 2 has how they are related.

    Each "child" has 2 "parents", but each "parent" is also a child and has two parents, etc. etc.
    Code:
    Table 1:
    Key  Name
    1     Bob
    2     Mary
    3     Andy
    4     Todd
    5     Alex
    6     Sarah
    
    Table 2:
    Key  Child  Parent
    1     5       4
    2     5       6
    3     4       1
    4     4       2
    5     3       1
    6     3       2
    Using the relationships in Table 2, we know that Bob and Mary had Andy and Todd. We also know that Todd and Sarah had Alex.

    Your form for adding new members to a given family can be a form/sub form. Use the form to select a CHILD, and the sub-form selects the PARENTS (because in this case, the nomenclature is the opposite of conventional as each child has multiple parents).

    You can denote step-parents using an additional field on Table 2.

    You show siblings by a query of all children with a given set of parents and so forth.

    Hope this helps,
    tc
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Actually, this isn't a true many-to-many relationship since the two outside table are in fact the same table.

  7. #7
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    As far as "walking the tree" to capture more than one generation, it gets a little complicated, as what you're asking for is an "Indented Bill Of Materials"

    Try a few things out and if you need help, let me know.

    tc

  8. #8
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,422
    Provided Answers: 7
    I wrote a family tree database it only got 2 table main tables

    my thinking was each person is a child

    1 Family table
    2 Child table


    the family table only had the

    familyID
    motherID which is the CHildid form the Child table
    fatherID which is the CHildid form the Child table
    married
    when
    where
    ...


    having a family table you can then pick the step child out of the table






    and the Child table

    ChildID
    dob birth
    dod death
    fname
    suraname
    given name
    bla
    bla

    Then I mod it to
    work on this web site

    http://www.mrilivestock.co.nz/parent.asp?BullID=192

    The mod was take out the family table
    so i just added
    motherid which is a childid
    fatherid which is a childid
    into the child table


    the tree get build in real time.
    the coding works like this

    has childid got a fatherID then as the fatherID is a childID we start again childid got a fatherID until fatherID is 0
    same with the mother side
    Last edited by myle; 06-23-06 at 16:52.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    do you need to cater for the realtively modern phenominum where there may be more than one realtionship between two memebrs fothe family

    eg where the mother remarries, then divorces, and what was husmband marries daughter

    or what used to happen where a cousin may marry cousing or uncle/aunt
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,422
    Provided Answers: 7
    That why you have a Family table

    eg
    famlyID motherID fatherID
    1,2,5
    2,2,100
    5,121,123

    childid name famliyID
    1,stephan,1
    19,bob,2
    2,jan,5
    7,jack,5

    so bob is my step bother of stephan

    because of the mother id in the famliy table
    as jack is in famly 5 same as jan there for jack is aunt to staphan
    Last edited by myle; 06-23-06 at 17:19.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by healdem
    do you need to cater for the realtively modern phenominum where there may be more than one realtionship between two memebrs fothe family

    eg where the mother remarries, then divorces, and what was husmband marries daughter

    or what used to happen where a cousin may marry cousing or uncle/aunt
    HEY, We're not in [Insert State of Choice Here] ...

    Myle's construct seems to be rather limited ... Seems rather linear betwixt parent-child ... What about other relationships????
    Back to Access ... ADO is not the way to go for speed ...

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by M Owen
    HEY, We're not in [Insert State of Choice Here] ...

    Myle's construct seems to be rather limited ... Seems rather linear betwixt parent-child ... What about other relationships????

    glad to see ytou limited to "state" of your choice, rahter than "country".....
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Jun 2006
    Posts
    3

    Wink Entertaining...

    Thanks for your insight, but lets keep simple please

    I only really need to model a small part of the wonderful world of relationships - children and thier adult family members for a short period of time - well as long as the children are children! What's also important is that the data entry should be really simple - ideally being able to enter the child's, their sibblings and their parents details on single form... is this a job for nested queries on a single form? If so, how would I go about doing this?

    Simon
    Last edited by sharrisonUK; 06-23-06 at 19:16.

  14. #14
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,422
    Provided Answers: 7
    Quote Originally Posted by M Owen
    HEY, We're not in [Insert State of Choice Here] ...

    Myle's construct seems to be rather limited ... Seems rather linear betwixt parent-child ... What about other relationships????

    Owen
    What other relationships do you need


    sharrisonUK
    why not just use a listbox pointing to the ID of the Form
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  15. #15
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    That's a lot to ask of a single form, unless you can get it all together in one query. The alternative is to have part of the form unbound and run the updating through code.

    tc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •