Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    May 2004
    Location
    Asheville, North Carolina, USA
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parents & Children (Access 97)

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    I have read through some of the other post that I found dealing with my problem but I am still at a lose. I am wanting to create a table that list the parents names (mother & father) and a table that list the children names (kids). I know I have to create a third table that links the two table together, how do I do this and is there any easy way? I am dealing with approximately 150 families.
    <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15>

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parents & Children (Access 97)

    One approach is to use a "self-join" in a single table of people, with fields like this:

    tblPeople
    PeopleID - Primary Key, autonumber
    FName
    LName, etc
    MotherID - LongInteger
    FatherID - LongInteger

    You store the peopleID from each of the parents in the appropriate field of the children. Then using queries, you can list all the people and their parents. The "self-join" part comes because the table is joined to itself to glean additional information about each person.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parents & Children (Access 97)

    It isn't as simple as that, since children can have parents, stepparents, foster parents, adoptive parents, and multiple sets of everything but the original set. The table design would depend on specifically what you were trying to record. It is possible to use a single table, but that can get very messy given the multiple potential connections. Another approach would be to have two tables, one for parents and one for children. Each record in the parent table would contain the information on one parent, including the category (mother, father, stepmother, stepfather, etc.) Each child in the child table would list the information about the child--name, birth date, etc. You would then create a family table that would consist of a parentID, a childID and any information you wanted to store about the relationship, like custodial parent. There would be one record for each parent-child combination. Some children might have only a single parent-child record, while others could have a record for the father-child and another for the mother-child. In the case of divorce and remarriage, you could have several sets of parent-child records for the same child. Then to get a "family" listing for a child, you would query the join table for that child's ID and return all the parent records associated. Is that something like what you were trying to do?
    Charlotte

  4. #4
    Star Lounger
    Join Date
    May 2004
    Location
    Asheville, North Carolina, USA
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parents & Children (Access 97)

    Charlotte,
    I think this is what I need to do. I am going to give it a try and see what happens. Wish me luck.
    I will be back if I have any problems.
    Thanks for everyones help and assistance.
    <img src=/S/yep.gif border=0 alt=yep width=15 height=15>

  5. #5
    Star Lounger
    Join Date
    May 2004
    Location
    Asheville, North Carolina, USA
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parents & Children (Access 97)

    HELP <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>
    I have created the Parent Table & the Child Table, how do I create and join these 2 table to the 3rd table that has to be created. I would send an attachment but striped down it is still above 100K limit. I think I am on the right track. This is something new to me, but I am loving to learn.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parents & Children (Access 97)

    The parent table should contain a primary key ParentID (for example an AutoNumber field).
    The child table should contain a primary key ChildID (for example an AutoNumber field).

    Create a new table, and create at least two fields in it: ParentID and ChiIdID. If the primary keys in the parent and child tables are AutoNumber fields, these should be number (long integer) fields. They should not be AutoNumber fields in the third table!. If you want to store information specific to a parent-child combination, you can add extra fields.
    Select the ParentID and ChildID fields together, then click the Primary Key button on the toolbar. This will make the combination of the parent and child unique. Each parent may have seceral records in this table, as may each child, but the combination of a particular parent and a particular child must be unique.
    Save the table.

    Select Tools | Relationships...
    Add the 3 tables to the Relationships window (if that hasn't been done already)
    Click on the ParentID field in the parent table and drag it to the ParentID field in the intermediate table you just created. Tick the "Enforce referential integrity" check box, then click OK.
    Click on the ChildID field in the children table and drag it to the ChildID field in the intermediate table you just created. Tick the "Enforce referential integrity" check box, then click OK.

  7. #7
    Star Lounger
    Join Date
    May 2004
    Location
    Asheville, North Carolina, USA
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parents & Children (Access 97)

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20> Hans,
    OK, got that working. Is there any easy way to fill out the 3rd table, ParentChildID numbers? Going back and retrieving the auto-numbers created in the other 2 tables to fill in the 3rd table is time consuming. Do you or does anyone have any ideas to make it easier?
    Thanks

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parents & Children (Access 97)

    The parent and child tables do not hold information on how they are to be combined, otherwise you wouldn't have needed the intermediate table. Do you have some kind of other table that can be used to populate the intermediate table? If not, you're stuck with populating it manually.

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Parents & Children (Access 97)

    For what it's worth at this late point, we usually solve this problem by having a people table and a relationship table. The people table is pretty obvious - the relationship table contains three basic fields, plus any other pertinent info about the relationship. Those three fields are a Person1 field, a Person2 field, and a Relationship type. That lets you do pretty much anything, and you can define whatever relationships are pertinent to you situation.
    Wendell

Posting Permissions

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