One Tbl? Or three?

Discussion in 'Access Table Design' started by Rob S, Nov 26, 2010.

  1. Rob S

    Rob S Guest

    I am trying to collect data on people in a family unit. So to start
    with I need FName, LName, Relationship, and FileNumber. To simplify
    my question, relationship will always be one of three people: Mother,
    Father, Child. FileNumber is the piece that will link them together.
    I need to be able to search by last name and have the query identify
    all three people, even if the last name of Mother is different than
    the last name of Father and/or Child. Others will be using the db and
    so I'm trying to make data entry simple, which might be part of my

    My question could be a forms question, a query question, a tables
    question or a question for all three. Because I'm dealing with the
    data, I'll try here and I hope someone can help me through this.

    Since simplicity of data entry is the key I wanted certain pieces of
    info to automatically entered so the person entering the data has
    fewer keystrokes, mouse clicks or whatever. I thought I could create
    one table with for all three persons and then create a main form for
    mom (the person I'm most interested in) with separate subforms for
    Father and Child. The subforms would be linked based on FileNumber.
    I also wanted each form to enter a default value as to whether it was
    "Mother", "Father", or Child based on which form or subform I was

    The problem that I quickly ran into is that when I open the main form
    all of the subforms default to the same name since it is going for the
    first record set for that FileNumber.

    So my question is whether I want three separate tables that contain
    identical fields or do I want one table and then find a way to program
    the subforms to only show the names of the people appropriate for that

    I have considered the three separate tables and then used a union
    query to search the three tables for the appropriate last name. I
    then based a "make table" query on the union table. This generated a
    primary list specific to the name I searched so then it would be
    developing yet another query (I think) that would then link that
    individual to others with the same FileNumber but a different last

    I am using 2010 if that is an important part of the conversation.
    Rob S, Nov 26, 2010
    1. Advertisements

  2. Rob S

    Tony Toews Guest

    You want one person table with a self join and a field indicating if
    they are mother father and child.

    The self join is done by having two additional fields on the person
    table referencing the father and mother in other records. (Note that
    difficulties will arise where the parents are of the same gender or
    possibly not available.)

    Part of the reason for the self join is if this was a genealogy type
    of system which had the grand parents, etc. Having multiple tables
    gets very tedious very quickly.

    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages -
    Tony's Microsoft Access Blog -
    For a convenient utility to keep your users FEs and other files
    updated see
    Tony Toews, Nov 27, 2010
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.