Questionnaire

Now we're getting somewhere. I will suggest a table structure. The table
name is listed first, with the fields below it and indented. One thing that
is not clear is whether this program is just for the children with the
allergies, or if it is for the parents or guardians also. For now I will
assume the former.

tblApplication
ApplicantID (primary key, or PK)
AppFirstName
AppLastName
AppStreet
etc.

tblChildren
ChildID (PK)
ApplicantID (foreign key, or FK)
ChFirstName
ChLastName
etc.

I will leave the out for the moment the Questions part of the process.
Build these two tables. You define the PK in table design view. We'll get
to the FK in a moment. For the PK you can use autonumber or any other
number (or text) that is guaranteed to be unique. Access won't let you have
a repeating PK value.

Click Tools Relationships. Add both tables, then drag ApplicantID from
one table and drop it on ApplicantID in the other table. In the dialog box
that appears, click Enforce Referential Integrity.

Build a form (frmApplicant) based on tblApplicant, and another form
(fsubChild) based on tblChild. In form design view, drag the icon for
fsubChild onto frmApplicant. Now you can enter an applicant's information,
and separate records for each child. Format for both forms will probably be
single view (ont eh form's property sheet - View Properties. Experiment
with a few sample records.

Now for the questions. If there aren't too many of them (less than 30, say,
for each child) then they could be part of tblChild. Same for the applicant
questions, which could be part of tblApplicant. Alternatively, questions
could be in their own table that is related one-to-one with tblChild (see
Help for more on one-to-one relationships). Same for applicant questions,
which would probably be in their own table (if you are putting them into a
separate table from tblApplicant, that is), as I assume they would be rather
different than the Child questions.

Spaces for staff comments could be in tblApplicant, or in a table related
one-to-one. Remember that on the form (where you will be entering and
editing information) and on the report (for printing information) you can
arrange the fields any way you want. You are not limited to grids. Two
fields that are side-by-side on the table could be on opposite corners of
the form.

See if this helps to get you started.

"Flipper" wrote in message
...
Ok. WE have a program that helps low/moderate income families with
asthma/allergies. the fill out an app and we see if they qualify. The
app
asks Renter info and homeowner info (name, address, phone) (for each, if
applicable). Then, there is a children section for each child. It asks
name, dob,sex,race. It asks "your relationship to child", and anme and
phone
number of legal guardian/parent of child. Then there are a series of
questions for that child, with the answer of yes, no, or don't know.
(This
repeats for each child). then there are 13 questions in another section
of
the app for the applicant (usualy the parent). Then there are small
questions in part 4 that asks income and then for staff use only there
are
more questions; date app rcvd, date applicant contacted,
inspection/assesment?, basic intervention? , custom intvnt?

"BruceM" wrote:

Sorry, no. Why don't you start by describing the situation? There is an
application. Who is applying, and for what? Why are children listed on
the
application? What sorts of questions?
If a civic group is submitting applications for children to attend summer
camp, you might say something like: "A civic group submits applications
for
children to attend summer camp. There could be just one child, or as
many
as twenty. Each child is listed separately. Each child's listing
includes
Name, DOB and other personal information. A series of questions needs to
be
answered about each child."
In describing the situation, you do not need a lot of detail. "Name, DOB
and other personal information" is plenty to describe the Children table.
By the way, I need to modify something I said before. The relationship
to
the Questions table would probably be one-to-one, but to what other table
I
am not sure.

"Flipper" wrote in message
...
Is there any way I can send you my Word document of the form i am
basing
my
DB on and a copy of my db?


"BruceM" wrote:

Relationships exist between tables, not forms. Nested subforms are
fine,
but are not going to create relationships. The relationships come
first,
at
the table level. That's why I asked about your tables, but maybe it's
best
to back up a step first. What real-world situation is your database
intended to address. It sounds as if maybe you want to have an
application
form, and a subform on which children are listed, and another subform
for
information about each child. In that case you would have an
Application
table related one-to-many to a Children table, which is in turn
related
one-to-many with a Questions table. In terms of forms and subforms,
the
Questions subform would be nested in the Children form, which in turn
is
nested in the Application form.
Of course, my understanding may be completely inaccurate. In any
case,
there are a lot of unresolved details in my quick explanation.

"Flipper" wrote in message
news Well, I hope my relationships are set up right...But it could be
wrong...
What I mean by subforms is that I have them nested. Basically, I
have
a
main form called Applications. It has tab controls on that main
form.
Each
tab control has a subform on it. One the Subform called Children
(which
has
infor about each kid such as name, dob, gender, ethnic). I then put
the
Questions form on the children subform. (nested it). But all I get
is
blank
fields when I do. The form on its own, not as a subform works fine.
It
asks
a question and i have a box for the answers. I don't know how to
get
it
in
my main form and have it relate to each child.

"BruceM" wrote:

Your meaning is unclear, particularly "when I create a subform and
make
it
in the subform". I know you said that your relationships are set
up
properly, but something isn't working, so more information is
needed.
A
description of the table structure (not every field, just the
primary
keys,
other fields involved in relationships, and a general description
of
the
table's purpose) would help.

"Flipper" wrote in message
news I have tables set up for my questionnaire and responses. I made
the
questionnaire into a form and it lists all the questions. When I
create a
subform and make it in the subform, it doesnt list the questions.
It
just
lists blank fields. What am I doing wrong?

Well, I already started and would prefer not to start over, if I don't have
to. Do you have an email that I can send my db to and the word document of
the paper form I am creating most of my db around?

"BruceM" wrote:

Now we're getting somewhere. I will suggest a table structure. The table
name is listed first, with the fields below it and indented. One thing that
is not clear is whether this program is just for the children with the
allergies, or if it is for the parents or guardians also. For now I will
assume the former.

tblApplication
ApplicantID (primary key, or PK)
AppFirstName
AppLastName
AppStreet
etc.

tblChildren
ChildID (PK)
ApplicantID (foreign key, or FK)
ChFirstName
ChLastName
etc.

I will leave the out for the moment the Questions part of the process.
Build these two tables. You define the PK in table design view. We'll get
to the FK in a moment. For the PK you can use autonumber or any other
number (or text) that is guaranteed to be unique. Access won't let you have
a repeating PK value.

Click Tools Relationships. Add both tables, then drag ApplicantID from
one table and drop it on ApplicantID in the other table. In the dialog box
that appears, click Enforce Referential Integrity.

Build a form (frmApplicant) based on tblApplicant, and another form
(fsubChild) based on tblChild. In form design view, drag the icon for
fsubChild onto frmApplicant. Now you can enter an applicant's information,
and separate records for each child. Format for both forms will probably be
single view (ont eh form's property sheet - View Properties. Experiment
with a few sample records.

Now for the questions. If there aren't too many of them (less than 30, say,
for each child) then they could be part of tblChild. Same for the applicant
questions, which could be part of tblApplicant. Alternatively, questions
could be in their own table that is related one-to-one with tblChild (see
Help for more on one-to-one relationships). Same for applicant questions,
which would probably be in their own table (if you are putting them into a
separate table from tblApplicant, that is), as I assume they would be rather
different than the Child questions.

Spaces for staff comments could be in tblApplicant, or in a table related
one-to-one. Remember that on the form (where you will be entering and
editing information) and on the report (for printing information) you can
arrange the fields any way you want. You are not limited to grids. Two
fields that are side-by-side on the table could be on opposite corners of
the form.

See if this helps to get you started.

"Flipper" wrote in message
...
Ok. WE have a program that helps low/moderate income families with
asthma/allergies. the fill out an app and we see if they qualify. The
app
asks Renter info and homeowner info (name, address, phone) (for each, if
applicable). Then, there is a children section for each child. It asks
name, dob,sex,race. It asks "your relationship to child", and anme and
phone
number of legal guardian/parent of child. Then there are a series of
questions for that child, with the answer of yes, no, or don't know.
(This
repeats for each child). then there are 13 questions in another section
of
the app for the applicant (usualy the parent). Then there are small
questions in part 4 that asks income and then for staff use only there
are
more questions; date app rcvd, date applicant contacted,
inspection/assesment?, basic intervention? , custom intvnt?

"BruceM" wrote:

Sorry, no. Why don't you start by describing the situation? There is an
application. Who is applying, and for what? Why are children listed on
the
application? What sorts of questions?
If a civic group is submitting applications for children to attend summer
camp, you might say something like: "A civic group submits applications
for
children to attend summer camp. There could be just one child, or as
many
as twenty. Each child is listed separately. Each child's listing
includes
Name, DOB and other personal information. A series of questions needs to
be
answered about each child."
In describing the situation, you do not need a lot of detail. "Name, DOB
and other personal information" is plenty to describe the Children table.
By the way, I need to modify something I said before. The relationship
to
the Questions table would probably be one-to-one, but to what other table
I
am not sure.

"Flipper" wrote in message
...
Is there any way I can send you my Word document of the form i am
basing
my
DB on and a copy of my db?


"BruceM" wrote:

Relationships exist between tables, not forms. Nested subforms are
fine,
but are not going to create relationships. The relationships come
first,
at
the table level. That's why I asked about your tables, but maybe it's
best
to back up a step first. What real-world situation is your database
intended to address. It sounds as if maybe you want to have an
application
form, and a subform on which children are listed, and another subform
for
information about each child. In that case you would have an
Application
table related one-to-many to a Children table, which is in turn
related
one-to-many with a Questions table. In terms of forms and subforms,
the
Questions subform would be nested in the Children form, which in turn
is
nested in the Application form.
Of course, my understanding may be completely inaccurate. In any
case,
there are a lot of unresolved details in my quick explanation.

"Flipper" wrote in message
news Well, I hope my relationships are set up right...But it could be
wrong...
What I mean by subforms is that I have them nested. Basically, I
have
a
main form called Applications. It has tab controls on that main
form.
Each
tab control has a subform on it. One the Subform called Children
(which
has
infor about each kid such as name, dob, gender, ethnic). I then put
the
Questions form on the children subform. (nested it). But all I get
is
blank
fields when I do. The form on its own, not as a subform works fine.
It
asks
a question and i have a box for the answers. I don't know how to
get
it
in
my main form and have it relate to each child.

"BruceM" wrote:

Your meaning is unclear, particularly "when I create a subform and
make
it
in the subform". I know you said that your relationships are set
up
properly, but something isn't working, so more information is
needed.
A
description of the table structure (not every field, just the
primary
keys,
other fields involved in relationships, and a general description
of
the
table's purpose) would help.

"Flipper" wrote in message
news I have tables set up for my questionnaire and responses. I made
the
questionnaire into a form and it lists all the questions. When I
create a
subform and make it in the subform, it doesnt list the questions.
It
just
lists blank fields. What am I doing wrong?

You will probably spend more time trying to force an ineffective design to
meet your needs than you will starting over. You need a minimum of two
tables, set up along the lines I have suggested. Post your table design in
similar format to what I have done. Describe the relationships. Maybe I
misunderstand the situation, and what you have is workable. Regarding
sending the files to me: I have put some time into responding, and have
told you what specific information would help me (or somebody else) provide
a targeted answer. You can probably decipher the obfuscated e-mail address
in the message header, but unless you are willing to at least attempt to
provide information in this forum I will tell you frankly that I will not
make any promises about reviewing the files.

"Flipper" wrote in message
...
Well, I already started and would prefer not to start over, if I don't
have
to. Do you have an email that I can send my db to and the word document
of
the paper form I am creating most of my db around?

"BruceM" wrote:

Now we're getting somewhere. I will suggest a table structure. The
table
name is listed first, with the fields below it and indented. One thing
that
is not clear is whether this program is just for the children with the
allergies, or if it is for the parents or guardians also. For now I will
assume the former.

tblApplication
ApplicantID (primary key, or PK)
AppFirstName
AppLastName
AppStreet
etc.

tblChildren
ChildID (PK)
ApplicantID (foreign key, or FK)
ChFirstName
ChLastName
etc.

I will leave the out for the moment the Questions part of the process.
Build these two tables. You define the PK in table design view. We'll
get
to the FK in a moment. For the PK you can use autonumber or any other
number (or text) that is guaranteed to be unique. Access won't let you
have
a repeating PK value.

Click Tools Relationships. Add both tables, then drag ApplicantID from
one table and drop it on ApplicantID in the other table. In the dialog
box
that appears, click Enforce Referential Integrity.

Build a form (frmApplicant) based on tblApplicant, and another form
(fsubChild) based on tblChild. In form design view, drag the icon for
fsubChild onto frmApplicant. Now you can enter an applicant's
information,
and separate records for each child. Format for both forms will probably
be
single view (ont eh form's property sheet - View Properties.
Experiment
with a few sample records.

Now for the questions. If there aren't too many of them (less than 30,
say,
for each child) then they could be part of tblChild. Same for the
applicant
questions, which could be part of tblApplicant. Alternatively, questions
could be in their own table that is related one-to-one with tblChild (see
Help for more on one-to-one relationships). Same for applicant
questions,
which would probably be in their own table (if you are putting them into
a
separate table from tblApplicant, that is), as I assume they would be
rather
different than the Child questions.

Spaces for staff comments could be in tblApplicant, or in a table related
one-to-one. Remember that on the form (where you will be entering and
editing information) and on the report (for printing information) you can
arrange the fields any way you want. You are not limited to grids. Two
fields that are side-by-side on the table could be on opposite corners of
the form.

See if this helps to get you started.

"Flipper" wrote in message
...
Ok. WE have a program that helps low/moderate income families with
asthma/allergies. the fill out an app and we see if they qualify. The
app
asks Renter info and homeowner info (name, address, phone) (for each,
if
applicable). Then, there is a children section for each child. It
asks
name, dob,sex,race. It asks "your relationship to child", and anme and
phone
number of legal guardian/parent of child. Then there are a series of
questions for that child, with the answer of yes, no, or don't know.
(This
repeats for each child). then there are 13 questions in another
section
of
the app for the applicant (usualy the parent). Then there are small
questions in part 4 that asks income and then for staff use only there
are
more questions; date app rcvd, date applicant contacted,
inspection/assesment?, basic intervention? , custom intvnt?

"BruceM" wrote:

Sorry, no. Why don't you start by describing the situation? There is
an
application. Who is applying, and for what? Why are children listed
on
the
application? What sorts of questions?
If a civic group is submitting applications for children to attend
summer
camp, you might say something like: "A civic group submits
applications
for
children to attend summer camp. There could be just one child, or as
many
as twenty. Each child is listed separately. Each child's listing
includes
Name, DOB and other personal information. A series of questions needs
to
be
answered about each child."
In describing the situation, you do not need a lot of detail. "Name,
DOB
and other personal information" is plenty to describe the Children
table.
By the way, I need to modify something I said before. The
relationship
to
the Questions table would probably be one-to-one, but to what other
table
I
am not sure.

"Flipper" wrote in message
...
Is there any way I can send you my Word document of the form i am
basing
my
DB on and a copy of my db?


"BruceM" wrote:

Relationships exist between tables, not forms. Nested subforms are
fine,
but are not going to create relationships. The relationships come
first,
at
the table level. That's why I asked about your tables, but maybe
it's
best
to back up a step first. What real-world situation is your
database
intended to address. It sounds as if maybe you want to have an
application
form, and a subform on which children are listed, and another
subform
for
information about each child. In that case you would have an
Application
table related one-to-many to a Children table, which is in turn
related
one-to-many with a Questions table. In terms of forms and
subforms,
the
Questions subform would be nested in the Children form, which in
turn
is
nested in the Application form.
Of course, my understanding may be completely inaccurate. In any
case,
there are a lot of unresolved details in my quick explanation.

"Flipper" wrote in message
news Well, I hope my relationships are set up right...But it could be
wrong...
What I mean by subforms is that I have them nested. Basically, I
have
a
main form called Applications. It has tab controls on that main
form.
Each
tab control has a subform on it. One the Subform called Children
(which
has
infor about each kid such as name, dob, gender, ethnic). I then
put
the
Questions form on the children subform. (nested it). But all I
get
is
blank
fields when I do. The form on its own, not as a subform works
fine.
It
asks
a question and i have a box for the answers. I don't know how to
get
it
in
my main form and have it relate to each child.

"BruceM" wrote:

Your meaning is unclear, particularly "when I create a subform
and
make
it
in the subform". I know you said that your relationships are
set
up
properly, but something isn't working, so more information is
needed.
A
description of the table structure (not every field, just the
primary
keys,
other fields involved in relationships, and a general
description
of
the
table's purpose) would help.

"Flipper" wrote in message
news I have tables set up for my questionnaire and responses. I
made
the
questionnaire into a form and it lists all the questions.
When I
create a
subform and make it in the subform, it doesnt list the
questions.
It
just
lists blank fields. What am I doing wrong?

My tblApplications is as such:

ApplicationID
PeopleID
Relationship to Children
Application Date
Application Score
contactdate
BaselineDate

My tblChildren is:

ChildID
ApplicationID
ProjectNumber
FirstName
Last
DOB
GEnder
Ethnicity

tblPeople
PeopleID
REsidentStatus
ApplicantRole
FirstName
LastName

tblAddresses
AddressID
PeopleID
StreetADd
City
State
Zip

tblePhones
PhoneID
PeopleID
PhoneTypeID
PhoneNUmber

tluPHoneTypes
PhoneTypeID
PHoneType

I have tons of other tables too for all the questions. There are two parts
sections of questions on the application. One is related to each child on
the application. The other section of questions is related to the
application and asks more household questions or about ALL the children, so
to speak. Did you want to see how those tables are set up also?







"BruceM" wrote:

You will probably spend more time trying to force an ineffective design to
meet your needs than you will starting over. You need a minimum of two
tables, set up along the lines I have suggested. Post your table design in
similar format to what I have done. Describe the relationships. Maybe I
misunderstand the situation, and what you have is workable. Regarding
sending the files to me: I have put some time into responding, and have
told you what specific information would help me (or somebody else) provide
a targeted answer. You can probably decipher the obfuscated e-mail address
in the message header, but unless you are willing to at least attempt to
provide information in this forum I will tell you frankly that I will not
make any promises about reviewing the files.

"Flipper" wrote in message
...
Well, I already started and would prefer not to start over, if I don't
have
to. Do you have an email that I can send my db to and the word document
of
the paper form I am creating most of my db around?

"BruceM" wrote:

Now we're getting somewhere. I will suggest a table structure. The
table
name is listed first, with the fields below it and indented. One thing
that
is not clear is whether this program is just for the children with the
allergies, or if it is for the parents or guardians also. For now I will
assume the former.

tblApplication
ApplicantID (primary key, or PK)
AppFirstName
AppLastName
AppStreet
etc.

tblChildren
ChildID (PK)
ApplicantID (foreign key, or FK)
ChFirstName
ChLastName
etc.

I will leave the out for the moment the Questions part of the process.
Build these two tables. You define the PK in table design view. We'll
get
to the FK in a moment. For the PK you can use autonumber or any other
number (or text) that is guaranteed to be unique. Access won't let you
have
a repeating PK value.

Click Tools Relationships. Add both tables, then drag ApplicantID from
one table and drop it on ApplicantID in the other table. In the dialog
box
that appears, click Enforce Referential Integrity.

Build a form (frmApplicant) based on tblApplicant, and another form
(fsubChild) based on tblChild. In form design view, drag the icon for
fsubChild onto frmApplicant. Now you can enter an applicant's
information,
and separate records for each child. Format for both forms will probably
be
single view (ont eh form's property sheet - View Properties.
Experiment
with a few sample records.

Now for the questions. If there aren't too many of them (less than 30,
say,
for each child) then they could be part of tblChild. Same for the
applicant
questions, which could be part of tblApplicant. Alternatively, questions
could be in their own table that is related one-to-one with tblChild (see
Help for more on one-to-one relationships). Same for applicant
questions,
which would probably be in their own table (if you are putting them into
a
separate table from tblApplicant, that is), as I assume they would be
rather
different than the Child questions.

Spaces for staff comments could be in tblApplicant, or in a table related
one-to-one. Remember that on the form (where you will be entering and
editing information) and on the report (for printing information) you can
arrange the fields any way you want. You are not limited to grids. Two
fields that are side-by-side on the table could be on opposite corners of
the form.

See if this helps to get you started.

"Flipper" wrote in message
...
Ok. WE have a program that helps low/moderate income families with
asthma/allergies. the fill out an app and we see if they qualify. The
app
asks Renter info and homeowner info (name, address, phone) (for each,
if
applicable). Then, there is a children section for each child. It
asks
name, dob,sex,race. It asks "your relationship to child", and anme and
phone
number of legal guardian/parent of child. Then there are a series of
questions for that child, with the answer of yes, no, or don't know.
(This
repeats for each child). then there are 13 questions in another
section
of
the app for the applicant (usualy the parent). Then there are small
questions in part 4 that asks income and then for staff use only there
are
more questions; date app rcvd, date applicant contacted,
inspection/assesment?, basic intervention? , custom intvnt?

"BruceM" wrote:

Sorry, no. Why don't you start by describing the situation? There is
an
application. Who is applying, and for what? Why are children listed
on
the
application? What sorts of questions?
If a civic group is submitting applications for children to attend
summer
camp, you might say something like: "A civic group submits
applications
for
children to attend summer camp. There could be just one child, or as
many
as twenty. Each child is listed separately. Each child's listing
includes
Name, DOB and other personal information. A series of questions needs
to
be
answered about each child."
In describing the situation, you do not need a lot of detail. "Name,
DOB
and other personal information" is plenty to describe the Children
table.
By the way, I need to modify something I said before. The
relationship
to
the Questions table would probably be one-to-one, but to what other
table
I
am not sure.

"Flipper" wrote in message
...
Is there any way I can send you my Word document of the form i am
basing
my
DB on and a copy of my db?


"BruceM" wrote:

Relationships exist between tables, not forms. Nested subforms are
fine,
but are not going to create relationships. The relationships come
first,
at
the table level. That's why I asked about your tables, but maybe
it's
best
to back up a step first. What real-world situation is your
database
intended to address. It sounds as if maybe you want to have an
application
form, and a subform on which children are listed, and another
subform
for
information about each child. In that case you would have an
Application
table related one-to-many to a Children table, which is in turn
related
one-to-many with a Questions table. In terms of forms and
subforms,
the
Questions subform would be nested in the Children form, which in
turn
is
nested in the Application form.
Of course, my understanding may be completely inaccurate. In any
case,
there are a lot of unresolved details in my quick explanation.

"Flipper" wrote in message
news Well, I hope my relationships are set up right...But it could be
wrong...
What I mean by subforms is that I have them nested. Basically, I
have
a
main form called Applications. It has tab controls on that main
form.
Each
tab control has a subform on it. One the Subform called Children
(which
has
infor about each kid such as name, dob, gender, ethnic). I then
put
the
Questions form on the children subform. (nested it). But all I
get
is
blank
fields when I do. The form on its own, not as a subform works
fine.
It
asks
a question and i have a box for the answers. I don't know how to
get
it
in
my main form and have it relate to each child.

"BruceM" wrote:

Your meaning is unclear, particularly "when I create a subform
and
make
it
in the subform". I know you said that your relationships are
set
up
properly, but something isn't working, so more information is
needed.
A
description of the table structure (not every field, just the
primary
keys,
other fields involved in relationships, and a general
description
of
the
table's purpose) would help.

"Flipper" wrote in message
news I have tables set up for my questionnaire and responses. I
made
the
questionnaire into a form and it lists all the questions.
When I
create a
subform and make it in the subform, it doesnt list the
questions.
It
just
lists blank fields. What am I doing wrong?

No wonder you don't want to change it. You went to a lot of trouble putting
together all of those tables.

Here'a a pretty concise way of looking at tables: A table should contain
information about a single real-world entity (application, child, etc.).
You should be able to describe its purpose in a single sentence without
using the word "and". Having said that, you can have first name and last
name, or name and address, and things like that in the same table. Items
such as FirstName, LastName, Street, City, Zip, Phone and so forth are all
parts of a person's contact information, so they could all be in the same
record in the People table (or whatever you would like to call it). A main
reason for related tables is to avoid duplicating information. Consider
Social Security numbers. That number identifies you for tax and benefits
purposes, no matter if your name and address changes. If your name and
address were part of the record for every tax or benefit transaction, then
old records would contain information that may no longer be current.

There is no real reason to have a separate address table. In most cases you
will want that information in each person's record. If two people are at
the same address and one of them moves, what would you do if you had linked
both to the same address record? However, you may choose to have a Parent
table with name and address information. Children would be in a related
table, but the address would appear only once (in the Parent table).

The whole issue of parents and children can get a bit murky, since children
grow up and may become parents themselves. In that case, and in other cases
as well, you may choose to put all people into one table, and to use check
boxes or some such to separate them into categories. I will ignore that,
since it sounds as if you aren't dealing with that situation.

What I said about addresses applies to phone numbers and other personal
information.

Could the same person be an Applicant more than once? If so, you need to
store Applicant information in its own table. In that case you would have
an Application table, an Applicant table, and a Child table.

tblApplication
ApplicationID (PK)
AppDate
Reason, comments, and other information about that specific application

tblApplicant
ApplicantID (PK)
ApplicationID (FK)
AppFirstName
other personal information

tblChild
ChildID (PK)
ApplicantID (FK)
ChFirstName and other information specific to the child

Create relationships between the PK fields and their namesake FK fields.
Click Enforce Referential Integrity for all.

Build an Application form, and subforms for Applicant and Children as I
described. Don't worry about data for now. Just create the tables and
forms, insert the subforms as described, and try adding a few sample
records. Get used to how this can work.

For handling the questions, see my remarks in the previous post. For now,
don't worrry about that. Make some related records, and get that part of it
working.

If there is a reason you cannot change your design, about all I can suggest
is to see Help for information about one-to-one relationships. Apply that
relationship between the address table and the People table, etc., and get
ready for an administrative nightmare.

"Flipper" wrote in message
...
My tblApplications is as such:

ApplicationID
PeopleID
Relationship to Children
Application Date
Application Score
contactdate
BaselineDate

My tblChildren is:

ChildID
ApplicationID
ProjectNumber
FirstName
Last
DOB
GEnder
Ethnicity

tblPeople
PeopleID
REsidentStatus
ApplicantRole
FirstName
LastName

tblAddresses
AddressID
PeopleID
StreetADd
City
State
Zip

tblePhones
PhoneID
PeopleID
PhoneTypeID
PhoneNUmber

tluPHoneTypes
PhoneTypeID
PHoneType

I have tons of other tables too for all the questions. There are two
parts
sections of questions on the application. One is related to each child on
the application. The other section of questions is related to the
application and asks more household questions or about ALL the children,
so
to speak. Did you want to see how those tables are set up also?







"BruceM" wrote:

You will probably spend more time trying to force an ineffective design
to
meet your needs than you will starting over. You need a minimum of two
tables, set up along the lines I have suggested. Post your table design
in
similar format to what I have done. Describe the relationships. Maybe I
misunderstand the situation, and what you have is workable. Regarding
sending the files to me: I have put some time into responding, and have
told you what specific information would help me (or somebody else)
provide
a targeted answer. You can probably decipher the obfuscated e-mail
address
in the message header, but unless you are willing to at least attempt to
provide information in this forum I will tell you frankly that I will not
make any promises about reviewing the files.

"Flipper" wrote in message
...
Well, I already started and would prefer not to start over, if I don't
have
to. Do you have an email that I can send my db to and the word
document
of
the paper form I am creating most of my db around?

"BruceM" wrote:

Now we're getting somewhere. I will suggest a table structure. The
table
name is listed first, with the fields below it and indented. One
thing
that
is not clear is whether this program is just for the children with the
allergies, or if it is for the parents or guardians also. For now I
will
assume the former.

tblApplication
ApplicantID (primary key, or PK)
AppFirstName
AppLastName
AppStreet
etc.

tblChildren
ChildID (PK)
ApplicantID (foreign key, or FK)
ChFirstName
ChLastName
etc.

I will leave the out for the moment the Questions part of the process.
Build these two tables. You define the PK in table design view.
We'll
get
to the FK in a moment. For the PK you can use autonumber or any other
number (or text) that is guaranteed to be unique. Access won't let
you
have
a repeating PK value.

Click Tools Relationships. Add both tables, then drag ApplicantID
from
one table and drop it on ApplicantID in the other table. In the
dialog
box
that appears, click Enforce Referential Integrity.

Build a form (frmApplicant) based on tblApplicant, and another form
(fsubChild) based on tblChild. In form design view, drag the icon for
fsubChild onto frmApplicant. Now you can enter an applicant's
information,
and separate records for each child. Format for both forms will
probably
be
single view (ont eh form's property sheet - View Properties.
Experiment
with a few sample records.

Now for the questions. If there aren't too many of them (less than
30,
say,
for each child) then they could be part of tblChild. Same for the
applicant
questions, which could be part of tblApplicant. Alternatively,
questions
could be in their own table that is related one-to-one with tblChild
(see
Help for more on one-to-one relationships). Same for applicant
questions,
which would probably be in their own table (if you are putting them
into
a
separate table from tblApplicant, that is), as I assume they would be
rather
different than the Child questions.

Spaces for staff comments could be in tblApplicant, or in a table
related
one-to-one. Remember that on the form (where you will be entering and
editing information) and on the report (for printing information) you
can
arrange the fields any way you want. You are not limited to grids.
Two
fields that are side-by-side on the table could be on opposite corners
of
the form.

See if this helps to get you started.

"Flipper" wrote in message
...
Ok. WE have a program that helps low/moderate income families with
asthma/allergies. the fill out an app and we see if they qualify.
The
app
asks Renter info and homeowner info (name, address, phone) (for
each,
if
applicable). Then, there is a children section for each child. It
asks
name, dob,sex,race. It asks "your relationship to child", and anme
and
phone
number of legal guardian/parent of child. Then there are a series
of
questions for that child, with the answer of yes, no, or don't know.
(This
repeats for each child). then there are 13 questions in another
section
of
the app for the applicant (usualy the parent). Then there are small
questions in part 4 that asks income and then for staff use only
there
are
more questions; date app rcvd, date applicant contacted,
inspection/assesment?, basic intervention? , custom intvnt?

"BruceM" wrote:

Sorry, no. Why don't you start by describing the situation? There
is
an
application. Who is applying, and for what? Why are children
listed
on
the
application? What sorts of questions?
If a civic group is submitting applications for children to attend
summer
camp, you might say something like: "A civic group submits
applications
for
children to attend summer camp. There could be just one child, or
as
many
as twenty. Each child is listed separately. Each child's listing
includes
Name, DOB and other personal information. A series of questions
needs
to
be
answered about each child."
In describing the situation, you do not need a lot of detail.
"Name,
DOB
and other personal information" is plenty to describe the Children
table.
By the way, I need to modify something I said before. The
relationship
to
the Questions table would probably be one-to-one, but to what other
table
I
am not sure.

"Flipper" wrote in message
...
Is there any way I can send you my Word document of the form i am
basing
my
DB on and a copy of my db?


"BruceM" wrote:

Relationships exist between tables, not forms. Nested subforms
are
fine,
but are not going to create relationships. The relationships
come
first,
at
the table level. That's why I asked about your tables, but
maybe
it's
best
to back up a step first. What real-world situation is your
database
intended to address. It sounds as if maybe you want to have an
application
form, and a subform on which children are listed, and another
subform
for
information about each child. In that case you would have an
Application
table related one-to-many to a Children table, which is in turn
related
one-to-many with a Questions table. In terms of forms and
subforms,
the
Questions subform would be nested in the Children form, which in
turn
is
nested in the Application form.
Of course, my understanding may be completely inaccurate. In
any
case,
there are a lot of unresolved details in my quick explanation.

"Flipper" wrote in message
news Well, I hope my relationships are set up right...But it could
be
wrong...
What I mean by subforms is that I have them nested.
Basically, I
have
a
main form called Applications. It has tab controls on that
main
form.
Each
tab control has a subform on it. One the Subform called
Children
(which
has
infor about each kid such as name, dob, gender, ethnic). I
then
put
the
Questions form on the children subform. (nested it). But all
I
get
is
blank
fields when I do. The form on its own, not as a subform works
fine.
It
asks
a question and i have a box for the answers. I don't know how
to
get
it
in
my main form and have it relate to each child.

"BruceM" wrote:

Your meaning is unclear, particularly "when I create a
subform
and
make
it
in the subform". I know you said that your relationships are
set
up
properly, but something isn't working, so more information is
needed.
A
description of the table structure (not every field, just the
primary
keys,
other fields involved in relationships, and a general
description
of
the
table's purpose) would help.

"Flipper" wrote in
message
news I have tables set up for my questionnaire and responses. I
made
the
questionnaire into a form and it lists all the questions.
When I
create a
subform and make it in the subform, it doesnt list the
questions.
It
just
lists blank fields. What am I doing wrong?

Yes, I have already made all of my tables and established one to many
relationships. I have already created my main form with subforms. I am just
trying to figure out the one section on the paper application, which is a
series of questions. I have the answer key to the questions, and just need
to get the responses on there for each question. Each question will have a
yes/no/don't know answer, but then a couple have a "if yes, then what
source?" (or somethi9ng like that). Then there are like 6 choices and then
"other" as the last choice. then I have another table for "other".
"BruceM" wrote:

No wonder you don't want to change it. You went to a lot of trouble putting
together all of those tables.

Here'a a pretty concise way of looking at tables: A table should contain
information about a single real-world entity (application, child, etc.).
You should be able to describe its purpose in a single sentence without
using the word "and". Having said that, you can have first name and last
name, or name and address, and things like that in the same table. Items
such as FirstName, LastName, Street, City, Zip, Phone and so forth are all
parts of a person's contact information, so they could all be in the same
record in the People table (or whatever you would like to call it). A main
reason for related tables is to avoid duplicating information. Consider
Social Security numbers. That number identifies you for tax and benefits
purposes, no matter if your name and address changes. If your name and
address were part of the record for every tax or benefit transaction, then
old records would contain information that may no longer be current.

There is no real reason to have a separate address table. In most cases you
will want that information in each person's record. If two people are at
the same address and one of them moves, what would you do if you had linked
both to the same address record? However, you may choose to have a Parent
table with name and address information. Children would be in a related
table, but the address would appear only once (in the Parent table).

The whole issue of parents and children can get a bit murky, since children
grow up and may become parents themselves. In that case, and in other cases
as well, you may choose to put all people into one table, and to use check
boxes or some such to separate them into categories. I will ignore that,
since it sounds as if you aren't dealing with that situation.

What I said about addresses applies to phone numbers and other personal
information.

Could the same person be an Applicant more than once? If so, you need to
store Applicant information in its own table. In that case you would have
an Application table, an Applicant table, and a Child table.

tblApplication
ApplicationID (PK)
AppDate
Reason, comments, and other information about that specific application

tblApplicant
ApplicantID (PK)
ApplicationID (FK)
AppFirstName
other personal information

tblChild
ChildID (PK)
ApplicantID (FK)
ChFirstName and other information specific to the child

Create relationships between the PK fields and their namesake FK fields.
Click Enforce Referential Integrity for all.

Build an Application form, and subforms for Applicant and Children as I
described. Don't worry about data for now. Just create the tables and
forms, insert the subforms as described, and try adding a few sample
records. Get used to how this can work.

For handling the questions, see my remarks in the previous post. For now,
don't worrry about that. Make some related records, and get that part of it
working.

If there is a reason you cannot change your design, about all I can suggest
is to see Help for information about one-to-one relationships. Apply that
relationship between the address table and the People table, etc., and get
ready for an administrative nightmare.

"Flipper" wrote in message
...
My tblApplications is as such:

ApplicationID
PeopleID
Relationship to Children
Application Date
Application Score
contactdate
BaselineDate

My tblChildren is:

ChildID
ApplicationID
ProjectNumber
FirstName
Last
DOB
GEnder
Ethnicity

tblPeople
PeopleID
REsidentStatus
ApplicantRole
FirstName
LastName

tblAddresses
AddressID
PeopleID
StreetADd
City
State
Zip

tblePhones
PhoneID
PeopleID
PhoneTypeID
PhoneNUmber

tluPHoneTypes
PhoneTypeID
PHoneType

I have tons of other tables too for all the questions. There are two
parts
sections of questions on the application. One is related to each child on
the application. The other section of questions is related to the
application and asks more household questions or about ALL the children,
so
to speak. Did you want to see how those tables are set up also?







"BruceM" wrote:

You will probably spend more time trying to force an ineffective design
to
meet your needs than you will starting over. You need a minimum of two
tables, set up along the lines I have suggested. Post your table design
in
similar format to what I have done. Describe the relationships. Maybe I
misunderstand the situation, and what you have is workable. Regarding
sending the files to me: I have put some time into responding, and have
told you what specific information would help me (or somebody else)
provide
a targeted answer. You can probably decipher the obfuscated e-mail
address
in the message header, but unless you are willing to at least attempt to
provide information in this forum I will tell you frankly that I will not
make any promises about reviewing the files.

"Flipper" wrote in message
...
Well, I already started and would prefer not to start over, if I don't
have
to. Do you have an email that I can send my db to and the word
document
of
the paper form I am creating most of my db around?

"BruceM" wrote:

Now we're getting somewhere. I will suggest a table structure. The
table
name is listed first, with the fields below it and indented. One
thing
that
is not clear is whether this program is just for the children with the
allergies, or if it is for the parents or guardians also. For now I
will
assume the former.

tblApplication
ApplicantID (primary key, or PK)
AppFirstName
AppLastName
AppStreet
etc.

tblChildren
ChildID (PK)
ApplicantID (foreign key, or FK)
ChFirstName
ChLastName
etc.

I will leave the out for the moment the Questions part of the process.
Build these two tables. You define the PK in table design view.
We'll
get
to the FK in a moment. For the PK you can use autonumber or any other
number (or text) that is guaranteed to be unique. Access won't let
you
have
a repeating PK value.

Click Tools Relationships. Add both tables, then drag ApplicantID
from
one table and drop it on ApplicantID in the other table. In the
dialog
box
that appears, click Enforce Referential Integrity.

Build a form (frmApplicant) based on tblApplicant, and another form
(fsubChild) based on tblChild. In form design view, drag the icon for
fsubChild onto frmApplicant. Now you can enter an applicant's
information,
and separate records for each child. Format for both forms will
probably
be
single view (ont eh form's property sheet - View Properties.
Experiment
with a few sample records.

Now for the questions. If there aren't too many of them (less than
30,
say,
for each child) then they could be part of tblChild. Same for the
applicant
questions, which could be part of tblApplicant. Alternatively,
questions
could be in their own table that is related one-to-one with tblChild
(see
Help for more on one-to-one relationships). Same for applicant
questions,
which would probably be in their own table (if you are putting them
into
a
separate table from tblApplicant, that is), as I assume they would be
rather
different than the Child questions.

Spaces for staff comments could be in tblApplicant, or in a table
related
one-to-one. Remember that on the form (where you will be entering and
editing information) and on the report (for printing information) you
can
arrange the fields any way you want. You are not limited to grids.
Two
fields that are side-by-side on the table could be on opposite corners
of
the form.

See if this helps to get you started.

"Flipper" wrote in message
...
Ok. WE have a program that helps low/moderate income families with
asthma/allergies. the fill out an app and we see if they qualify.
The
app
asks Renter info and homeowner info (name, address, phone) (for
each,
if
applicable). Then, there is a children section for each child. It
asks
name, dob,sex,race. It asks "your relationship to child", and anme
and
phone
number of legal guardian/parent of child. Then there are a series
of
questions for that child, with the answer of yes, no, or don't know.
(This
repeats for each child). then there are 13 questions in another
section
of
the app for the applicant (usualy the parent). Then there are small
questions in part 4 that asks income and then for staff use only
there
are
more questions; date app rcvd, date applicant contacted,
inspection/assesment?, basic intervention? , custom intvnt?

"BruceM" wrote:

Sorry, no. Why don't you start by describing the situation? There
is
an
application. Who is applying, and for what? Why are children
listed
on
the
application? What sorts of questions?
If a civic group is submitting applications for children to attend
summer
camp, you might say something like: "A civic group submits
applications
for
children to attend summer camp. There could be just one child, or
as
many
as twenty. Each child is listed separately. Each child's listing
includes
Name, DOB and other personal information. A series of questions
needs
to
be
answered about each child."
In describing the situation, you do not need a lot of detail.
"Name,
DOB

I will admit I had not considered that each person could have multiple
addresses. For the question section, see Help for information on option
groups. An option group selection (Yes, No, Don't Know) is stored in a
single field, same as a Yes/No field but with more choices. For the "If
yes, then what", that can be a table field, and a text box on the form can
be bound to the field.
Good luck.

"Flipper" wrote in message
...
Yes, I have already made all of my tables and established one to many
relationships. I have already created my main form with subforms. I am
just
trying to figure out the one section on the paper application, which is a
series of questions. I have the answer key to the questions, and just
need
to get the responses on there for each question. Each question will have
a
yes/no/don't know answer, but then a couple have a "if yes, then what
source?" (or somethi9ng like that). Then there are like 6 choices and
then
"other" as the last choice. then I have another table for "other".
"BruceM" wrote:

No wonder you don't want to change it. You went to a lot of trouble
putting
together all of those tables.

Here'a a pretty concise way of looking at tables: A table should contain
information about a single real-world entity (application, child, etc.).
You should be able to describe its purpose in a single sentence without
using the word "and". Having said that, you can have first name and last
name, or name and address, and things like that in the same table. Items
such as FirstName, LastName, Street, City, Zip, Phone and so forth are
all
parts of a person's contact information, so they could all be in the same
record in the People table (or whatever you would like to call it). A
main
reason for related tables is to avoid duplicating information. Consider
Social Security numbers. That number identifies you for tax and benefits
purposes, no matter if your name and address changes. If your name and
address were part of the record for every tax or benefit transaction,
then
old records would contain information that may no longer be current.

There is no real reason to have a separate address table. In most cases
you
will want that information in each person's record. If two people are at
the same address and one of them moves, what would you do if you had
linked
both to the same address record? However, you may choose to have a
Parent
table with name and address information. Children would be in a related
table, but the address would appear only once (in the Parent table).

The whole issue of parents and children can get a bit murky, since
children
grow up and may become parents themselves. In that case, and in other
cases
as well, you may choose to put all people into one table, and to use
check
boxes or some such to separate them into categories. I will ignore that,
since it sounds as if you aren't dealing with that situation.

What I said about addresses applies to phone numbers and other personal
information.

Could the same person be an Applicant more than once? If so, you need to
store Applicant information in its own table. In that case you would
have
an Application table, an Applicant table, and a Child table.

tblApplication
ApplicationID (PK)
AppDate
Reason, comments, and other information about that specific
application

tblApplicant
ApplicantID (PK)
ApplicationID (FK)
AppFirstName
other personal information

tblChild
ChildID (PK)
ApplicantID (FK)
ChFirstName and other information specific to the child

Create relationships between the PK fields and their namesake FK fields.
Click Enforce Referential Integrity for all.

Build an Application form, and subforms for Applicant and Children as I
described. Don't worry about data for now. Just create the tables and
forms, insert the subforms as described, and try adding a few sample
records. Get used to how this can work.

For handling the questions, see my remarks in the previous post. For
now,
don't worrry about that. Make some related records, and get that part of
it
working.

If there is a reason you cannot change your design, about all I can
suggest
is to see Help for information about one-to-one relationships. Apply
that
relationship between the address table and the People table, etc., and
get
ready for an administrative nightmare.

"Flipper" wrote in message
...
My tblApplications is as such:

ApplicationID
PeopleID
Relationship to Children
Application Date
Application Score
contactdate
BaselineDate

My tblChildren is:

ChildID
ApplicationID
ProjectNumber
FirstName
Last
DOB
GEnder
Ethnicity

tblPeople
PeopleID
REsidentStatus
ApplicantRole
FirstName
LastName

tblAddresses
AddressID
PeopleID
StreetADd
City
State
Zip

tblePhones
PhoneID
PeopleID
PhoneTypeID
PhoneNUmber

tluPHoneTypes
PhoneTypeID
PHoneType

I have tons of other tables too for all the questions. There are two
parts
sections of questions on the application. One is related to each child
on
the application. The other section of questions is related to the
application and asks more household questions or about ALL the
children,
so
to speak. Did you want to see how those tables are set up also?







"BruceM" wrote:

You will probably spend more time trying to force an ineffective
design
to
meet your needs than you will starting over. You need a minimum of
two
tables, set up along the lines I have suggested. Post your table
design
in
similar format to what I have done. Describe the relationships.
Maybe I
misunderstand the situation, and what you have is workable. Regarding
sending the files to me: I have put some time into responding, and
have
told you what specific information would help me (or somebody else)
provide
a targeted answer. You can probably decipher the obfuscated e-mail
address
in the message header, but unless you are willing to at least attempt
to
provide information in this forum I will tell you frankly that I will
not
make any promises about reviewing the files.

"Flipper" wrote in message
...
Well, I already started and would prefer not to start over, if I
don't
have
to. Do you have an email that I can send my db to and the word
document
of
the paper form I am creating most of my db around?

"BruceM" wrote:

Now we're getting somewhere. I will suggest a table structure.
The
table
name is listed first, with the fields below it and indented. One
thing
that
is not clear is whether this program is just for the children with
the
allergies, or if it is for the parents or guardians also. For now
I
will
assume the former.

tblApplication
ApplicantID (primary key, or PK)
AppFirstName
AppLastName
AppStreet
etc.

tblChildren
ChildID (PK)
ApplicantID (foreign key, or FK)
ChFirstName
ChLastName
etc.

I will leave the out for the moment the Questions part of the
process.
Build these two tables. You define the PK in table design view.
We'll
get
to the FK in a moment. For the PK you can use autonumber or any
other
number (or text) that is guaranteed to be unique. Access won't let
you
have
a repeating PK value.

Click Tools Relationships. Add both tables, then drag
ApplicantID
from
one table and drop it on ApplicantID in the other table. In the
dialog
box
that appears, click Enforce Referential Integrity.

Build a form (frmApplicant) based on tblApplicant, and another form
(fsubChild) based on tblChild. In form design view, drag the icon
for
fsubChild onto frmApplicant. Now you can enter an applicant's
information,
and separate records for each child. Format for both forms will
probably
be
single view (ont eh form's property sheet - View Properties.
Experiment
with a few sample records.

Now for the questions. If there aren't too many of them (less than
30,
say,
for each child) then they could be part of tblChild. Same for the
applicant
questions, which could be part of tblApplicant. Alternatively,
questions
could be in their own table that is related one-to-one with
tblChild
(see
Help for more on one-to-one relationships). Same for applicant
questions,
which would probably be in their own table (if you are putting them
into
a
separate table from tblApplicant, that is), as I assume they would
be
rather
different than the Child questions.

Spaces for staff comments could be in tblApplicant, or in a table
related
one-to-one. Remember that on the form (where you will be entering
and
editing information) and on the report (for printing information)
you
can
arrange the fields any way you want. You are not limited to grids.
Two
fields that are side-by-side on the table could be on opposite
corners
of
the form.

See if this helps to get you started.

"Flipper" wrote in message
...
Ok. WE have a program that helps low/moderate income families
with
asthma/allergies. the fill out an app and we see if they
qualify.
The
app
asks Renter info and homeowner info (name, address, phone) (for
each,
if
applicable). Then, there is a children section for each child.
It
asks
name, dob,sex,race. It asks "your relationship to child", and
anme
and
phone
number of legal guardian/parent of child. Then there are a
series
of
questions for that child, with the answer of yes, no, or don't
know.
(This
repeats for each child). then there are 13 questions in another
section
of
the app for the applicant (usualy the parent). Then there are
small
questions in part 4 that asks income and then for staff use only
there
are
more questions; date app rcvd, date applicant contacted,
inspection/assesment?, basic intervention? , custom intvnt?

"BruceM" wrote:

Sorry, no. Why don't you start by describing the situation?
There
is
an
application. Who is applying, and for what? Why are children
listed
on
the
application? What sorts of questions?
If a civic group is submitting applications for children to
attend
summer
camp, you might say something like: "A civic group submits
applications
for
children to attend summer camp. There could be just one child,
or
as
many
as twenty. Each child is listed separately. Each child's
listing
includes
Name, DOB and other personal information. A series of questions
needs
to
be
answered about each child."
In describing the situation, you do not need a lot of detail.
"Name,
DOB

Bruce,
When you refer me to "HELP", do you mean on this website or in ACCESS?
Thanks.

"BruceM" wrote:

I will admit I had not considered that each person could have multiple
addresses. For the question section, see Help for information on option
groups. An option group selection (Yes, No, Don't Know) is stored in a
single field, same as a Yes/No field but with more choices. For the "If
yes, then what", that can be a table field, and a text box on the form can
be bound to the field.
Good luck.

"Flipper" wrote in message
...
Yes, I have already made all of my tables and established one to many
relationships. I have already created my main form with subforms. I am
just
trying to figure out the one section on the paper application, which is a
series of questions. I have the answer key to the questions, and just
need
to get the responses on there for each question. Each question will have
a
yes/no/don't know answer, but then a couple have a "if yes, then what
source?" (or somethi9ng like that). Then there are like 6 choices and
then
"other" as the last choice. then I have another table for "other".
"BruceM" wrote:

No wonder you don't want to change it. You went to a lot of trouble
putting
together all of those tables.

Here'a a pretty concise way of looking at tables: A table should contain
information about a single real-world entity (application, child, etc.).
You should be able to describe its purpose in a single sentence without
using the word "and". Having said that, you can have first name and last
name, or name and address, and things like that in the same table. Items
such as FirstName, LastName, Street, City, Zip, Phone and so forth are
all
parts of a person's contact information, so they could all be in the same
record in the People table (or whatever you would like to call it). A
main
reason for related tables is to avoid duplicating information. Consider
Social Security numbers. That number identifies you for tax and benefits
purposes, no matter if your name and address changes. If your name and
address were part of the record for every tax or benefit transaction,
then
old records would contain information that may no longer be current.

There is no real reason to have a separate address table. In most cases
you
will want that information in each person's record. If two people are at
the same address and one of them moves, what would you do if you had
linked
both to the same address record? However, you may choose to have a
Parent
table with name and address information. Children would be in a related
table, but the address would appear only once (in the Parent table).

The whole issue of parents and children can get a bit murky, since
children
grow up and may become parents themselves. In that case, and in other
cases
as well, you may choose to put all people into one table, and to use
check
boxes or some such to separate them into categories. I will ignore that,
since it sounds as if you aren't dealing with that situation.

What I said about addresses applies to phone numbers and other personal
information.

Could the same person be an Applicant more than once? If so, you need to
store Applicant information in its own table. In that case you would
have
an Application table, an Applicant table, and a Child table.

tblApplication
ApplicationID (PK)
AppDate
Reason, comments, and other information about that specific
application

tblApplicant
ApplicantID (PK)
ApplicationID (FK)
AppFirstName
other personal information

tblChild
ChildID (PK)
ApplicantID (FK)
ChFirstName and other information specific to the child

Create relationships between the PK fields and their namesake FK fields.
Click Enforce Referential Integrity for all.

Build an Application form, and subforms for Applicant and Children as I
described. Don't worry about data for now. Just create the tables and
forms, insert the subforms as described, and try adding a few sample
records. Get used to how this can work.

For handling the questions, see my remarks in the previous post. For
now,
don't worrry about that. Make some related records, and get that part of
it
working.

If there is a reason you cannot change your design, about all I can
suggest
is to see Help for information about one-to-one relationships. Apply
that
relationship between the address table and the People table, etc., and
get
ready for an administrative nightmare.

"Flipper" wrote in message
...
My tblApplications is as such:

ApplicationID
PeopleID
Relationship to Children
Application Date
Application Score
contactdate
BaselineDate

My tblChildren is:

ChildID
ApplicationID
ProjectNumber
FirstName
Last
DOB
GEnder
Ethnicity

tblPeople
PeopleID
REsidentStatus
ApplicantRole
FirstName
LastName

tblAddresses
AddressID
PeopleID
StreetADd
City
State
Zip

tblePhones
PhoneID
PeopleID
PhoneTypeID
PhoneNUmber

tluPHoneTypes
PhoneTypeID
PHoneType

I have tons of other tables too for all the questions. There are two
parts
sections of questions on the application. One is related to each child
on
the application. The other section of questions is related to the
application and asks more household questions or about ALL the
children,
so
to speak. Did you want to see how those tables are set up also?







"BruceM" wrote:

You will probably spend more time trying to force an ineffective
design
to
meet your needs than you will starting over. You need a minimum of
two
tables, set up along the lines I have suggested. Post your table
design
in
similar format to what I have done. Describe the relationships.
Maybe I
misunderstand the situation, and what you have is workable. Regarding
sending the files to me: I have put some time into responding, and
have
told you what specific information would help me (or somebody else)
provide
a targeted answer. You can probably decipher the obfuscated e-mail
address
in the message header, but unless you are willing to at least attempt
to
provide information in this forum I will tell you frankly that I will
not
make any promises about reviewing the files.

"Flipper" wrote in message
...
Well, I already started and would prefer not to start over, if I
don't
have
to. Do you have an email that I can send my db to and the word
document
of
the paper form I am creating most of my db around?

"BruceM" wrote:

Now we're getting somewhere. I will suggest a table structure.
The
table
name is listed first, with the fields below it and indented. One
thing
that
is not clear is whether this program is just for the children with
the
allergies, or if it is for the parents or guardians also. For now
I
will
assume the former.

tblApplication
ApplicantID (primary key, or PK)
AppFirstName
AppLastName
AppStreet
etc.

tblChildren
ChildID (PK)
ApplicantID (foreign key, or FK)
ChFirstName
ChLastName
etc.

I will leave the out for the moment the Questions part of the
process.
Build these two tables. You define the PK in table design view.
We'll
get
to the FK in a moment. For the PK you can use autonumber or any
other
number (or text) that is guaranteed to be unique. Access won't let
you
have
a repeating PK value.

Click Tools Relationships. Add both tables, then drag
ApplicantID
from
one table and drop it on ApplicantID in the other table. In the
dialog
box
that appears, click Enforce Referential Integrity.

Build a form (frmApplicant) based on tblApplicant, and another form
(fsubChild) based on tblChild. In form design view, drag the icon
for
fsubChild onto frmApplicant. Now you can enter an applicant's
information,
and separate records for each child. Format for both forms will
probably
be
single view (ont eh form's property sheet - View Properties.
Experiment
with a few sample records.

Now for the questions. If there aren't too many of them (less than
30,
say,
for each child) then they could be part of tblChild. Same for the
applicant
questions, which could be part of tblApplicant. Alternatively,
questions
could be in their own table that is related one-to-one with
tblChild
(see
Help for more on one-to-one relationships). Same for applicant
questions,
which would probably be in their own table (if you are putting them
into
a
separate table from tblApplicant, that is), as I assume they would
be
rather
different than the Child questions.

Spaces for staff comments could be in tblApplicant, or in a table
related
one-to-one. Remember that on the form (where you will be entering
and
editing information) and on the report (for printing information)
you
can
arrange the fields any way you want. You are not limited to grids.
Two

I am back and did end up redesigning my DB. I feel it is much better and
closer to what you had suggested. So, now I am trying to tackle my applicant
questions/responses. There are 13 questions. Some of the questions have
multiple choice answers (yes/no/don't know) and two of them have text
answers, and one of them that has multiple choice for the answers of the
following: Reind/Relative, Hospital, Doctor/Nurse, etc, and the final
multiple choice is "other". If the applicant chooses "other", then I need to
have a spot that they can fill in what the other is, since there is a blank
line on the paper form asking them to describe "other". I think I need a
form to pop up, if they choose other. I have a table set up for when "other"
is chosen.

Also, with these 13 questions, there are two questions that as I explained
before, have a multiple choice answer of yes,no or don't know. But it then
goes on to say, IF yes, please answer the following multiple choices. It
goes on to give them choices, which they can choose more than one. One of
the choices is "other", so once again, I have that situation to tackle.

My tables for those are as follows:

tblApplication
tblApplicantQuestions
tblApplicantResponses
tblPart3Specify
tblPart3Other
and a junction table

Not sure if I went about this the right way. And I don't really know what I
am doing. Any suggestions how to set up these questions? Thank you.





"Flipper" wrote:

Bruce,
When you refer me to "HELP", do you mean on this website or in ACCESS?
Thanks.

"BruceM" wrote:

I will admit I had not considered that each person could have multiple
addresses. For the question section, see Help for information on option
groups. An option group selection (Yes, No, Don't Know) is stored in a
single field, same as a Yes/No field but with more choices. For the "If
yes, then what", that can be a table field, and a text box on the form can
be bound to the field.
Good luck.

"Flipper" wrote in message
...
Yes, I have already made all of my tables and established one to many
relationships. I have already created my main form with subforms. I am
just
trying to figure out the one section on the paper application, which is a
series of questions. I have the answer key to the questions, and just
need
to get the responses on there for each question. Each question will have
a
yes/no/don't know answer, but then a couple have a "if yes, then what
source?" (or somethi9ng like that). Then there are like 6 choices and
then
"other" as the last choice. then I have another table for "other".
"BruceM" wrote:

No wonder you don't want to change it. You went to a lot of trouble
putting
together all of those tables.

Here'a a pretty concise way of looking at tables: A table should contain
information about a single real-world entity (application, child, etc.).
You should be able to describe its purpose in a single sentence without
using the word "and". Having said that, you can have first name and last
name, or name and address, and things like that in the same table. Items
such as FirstName, LastName, Street, City, Zip, Phone and so forth are
all
parts of a person's contact information, so they could all be in the same
record in the People table (or whatever you would like to call it). A
main
reason for related tables is to avoid duplicating information. Consider
Social Security numbers. That number identifies you for tax and benefits
purposes, no matter if your name and address changes. If your name and
address were part of the record for every tax or benefit transaction,
then
old records would contain information that may no longer be current.

There is no real reason to have a separate address table. In most cases
you
will want that information in each person's record. If two people are at
the same address and one of them moves, what would you do if you had
linked
both to the same address record? However, you may choose to have a
Parent
table with name and address information. Children would be in a related
table, but the address would appear only once (in the Parent table).

The whole issue of parents and children can get a bit murky, since
children
grow up and may become parents themselves. In that case, and in other
cases
as well, you may choose to put all people into one table, and to use
check
boxes or some such to separate them into categories. I will ignore that,
since it sounds as if you aren't dealing with that situation.

What I said about addresses applies to phone numbers and other personal
information.

Could the same person be an Applicant more than once? If so, you need to
store Applicant information in its own table. In that case you would
have
an Application table, an Applicant table, and a Child table.

tblApplication
ApplicationID (PK)
AppDate
Reason, comments, and other information about that specific
application

tblApplicant
ApplicantID (PK)
ApplicationID (FK)
AppFirstName
other personal information

tblChild
ChildID (PK)
ApplicantID (FK)
ChFirstName and other information specific to the child

Create relationships between the PK fields and their namesake FK fields.
Click Enforce Referential Integrity for all.

Build an Application form, and subforms for Applicant and Children as I
described. Don't worry about data for now. Just create the tables and
forms, insert the subforms as described, and try adding a few sample
records. Get used to how this can work.

For handling the questions, see my remarks in the previous post. For
now,
don't worrry about that. Make some related records, and get that part of
it
working.

If there is a reason you cannot change your design, about all I can
suggest
is to see Help for information about one-to-one relationships. Apply
that
relationship between the address table and the People table, etc., and
get
ready for an administrative nightmare.

"Flipper" wrote in message
...
My tblApplications is as such:

ApplicationID
PeopleID
Relationship to Children
Application Date
Application Score
contactdate
BaselineDate

My tblChildren is:

ChildID
ApplicationID
ProjectNumber
FirstName
Last
DOB
GEnder
Ethnicity

tblPeople
PeopleID
REsidentStatus
ApplicantRole
FirstName
LastName

tblAddresses
AddressID
PeopleID
StreetADd
City
State
Zip

tblePhones
PhoneID
PeopleID
PhoneTypeID
PhoneNUmber

tluPHoneTypes
PhoneTypeID
PHoneType

I have tons of other tables too for all the questions. There are two
parts
sections of questions on the application. One is related to each child
on
the application. The other section of questions is related to the
application and asks more household questions or about ALL the
children,
so
to speak. Did you want to see how those tables are set up also?







"BruceM" wrote:

You will probably spend more time trying to force an ineffective
design
to
meet your needs than you will starting over. You need a minimum of
two
tables, set up along the lines I have suggested. Post your table
design
in
similar format to what I have done. Describe the relationships.
Maybe I
misunderstand the situation, and what you have is workable. Regarding
sending the files to me: I have put some time into responding, and
have
told you what specific information would help me (or somebody else)
provide
a targeted answer. You can probably decipher the obfuscated e-mail
address
in the message header, but unless you are willing to at least attempt
to
provide information in this forum I will tell you frankly that I will
not
make any promises about reviewing the files.

"Flipper" wrote in message
...
Well, I already started and would prefer not to start over, if I
don't
have
to. Do you have an email that I can send my db to and the word
document
of
the paper form I am creating most of my db around?

"BruceM" wrote:

Now we're getting somewhere. I will suggest a table structure.
The
table
name is listed first, with the fields below it and indented. One
thing
that
is not clear is whether this program is just for the children with
the
allergies, or if it is for the parents or guardians also. For now
I
will
assume the former.

tblApplication
ApplicantID (primary key, or PK)
AppFirstName
AppLastName
AppStreet
etc.

tblChildren
ChildID (PK)
ApplicantID (foreign key, or FK)
ChFirstName
ChLastName
etc.

I will leave the out for the moment the Questions part of the
process.
Build these two tables. You define the PK in table design view.
We'll
get
to the FK in a moment. For the PK you can use autonumber or any
other
number (or text) that is guaranteed to be unique. Access won't let
you
have
a repeating PK value.

Click Tools Relationships. Add both tables, then drag
ApplicantID
from
one table and drop it on ApplicantID in the other table. In the
dialog
box
that appears, click Enforce Referential Integrity.

Build a form (frmApplicant) based on tblApplicant, and another form
(fsubChild) based on tblChild. In form design view, drag the icon
for
fsubChild onto frmApplicant. Now you can enter an applicant's
information,
and separate records for each child. Format for both forms will
probably
be
single view (ont eh form's property sheet - View Properties.
Experiment
with a few sample records.

Now for the questions. If there aren't too many of them (less than
30,
say,
for each child) then they could be part of tblChild. Same for the
applicant
questions, which could be part of tblApplicant. Alternatively,
questions
could be in their own table that is related one-to-one with
tblChild
(see
Help for more on one-to-one relationships). Same for applicant
questions,
which would probably be in their own table (if you are putting them
into
a
separate table from tblApplicant, that is), as I assume they would
be
rather
different than the Child questions.

Spaces for staff comments could be in tblApplicant, or in a table
related
one-to-one. Remember that on the form (where you will be entering