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
    problem.

    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
    using.

    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
    subform?

    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
    name.

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

  2. Rob S

    Tony Toews Guest

    On Thu, 25 Nov 2010 17:36:25 -0800 (PST), Rob S
    <> wrote:

    >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
    >subform?


    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
    --
    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files
    updated see http://www.autofeupdater.com/
     
    Tony Toews, Nov 27, 2010
    #2
    1. Advertisements

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.
Similar Threads
  1. Guest

    One product table of three product tables

    Guest, Nov 28, 2003, in forum: Access Table Design
    Replies:
    1
    Views:
    112
    Michel Walsh
    Nov 28, 2003
  2. Max Moor

    One table or three

    Max Moor, Dec 16, 2003, in forum: Access Table Design
    Replies:
    1
    Views:
    78
    Michel Walsh
    Dec 16, 2003
  3. sd diamonds

    Working with recall info from a tbl - runtime error

    sd diamonds, Sep 3, 2004, in forum: Access Table Design
    Replies:
    0
    Views:
    86
    sd diamonds
    Sep 3, 2004
  4. cs
    Replies:
    2
    Views:
    127
  5. LMB

    Tbl design for expiration dates

    LMB, Feb 17, 2006, in forum: Access Table Design
    Replies:
    4
    Views:
    159
  6. LMB

    More tbl Design for expriation date

    LMB, Feb 19, 2006, in forum: Access Table Design
    Replies:
    5
    Views:
    93
    Duane Hookom
    Feb 27, 2006
  7. Jen27
    Replies:
    2
    Views:
    92
    John Vinson
    Mar 14, 2006
  8. Russ

    Linked Tbl Mgr behavior

    Russ, Dec 20, 2010, in forum: Access Table Design
    Replies:
    3
    Views:
    235
    Tony Toews
    Dec 21, 2010
Loading...