Questionnaire

[Guest]

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?

 

[jahoobob via AccessMonster.com]

Sounds like you need to link the two forms. Open the properties of the sub-
form from the form and click the ellipses (...) on Link Child fields and
enter the fields in each form that are the link.

 

[Guest]

I did that and they are linked properly.

Sounds like you need to link the two forms. Open the properties of the sub-
form from the form and click the ellipses (...) on Link Child fields and
enter the fields in each form that are the link.

 

[Guest]

I wonder too how to set up my combo box. It asks for which fields contain
the values I want to include in my combo box. My choices are AnswerKeyID,
QuestionsID, and AnswerText. I know I need to put in AnswerText, but do I
put both ID's in, or just one, and if so, which one?

 

[BruceM]

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.

 

[Guest]

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]

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.

 

[Guest]

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]

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.

 

[Guest]

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]

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.

 

[Guest]

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]

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.

 

[Guest]

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]

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.

 

[Guest]

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]

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.

 

[Guest]

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

 

[Guest]

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.