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