One Tbl? Or three?


R

Rob S

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

Advertisements

T

Tony Toews

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/
 

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. After that, you can post your question and our members will help you out.

Ask a Question

Top