Connecting different numbers of fields to one field

[Katherine Pegors]

Hi,

I'm trying to write a database to store surveys. The problem is that
different questions have different numbers of sub-parts. For each
sub-part, the person gives a response from 1 to 5 as well as comments
for the whole question. For example (not the real survey)
**************************************************************
1 = don't like; 2 = could be worse; 3 = ok; 4 = pretty well; 5 = a lot!
1) How much do you like eating...
a) Breakfast [4]
b) Lunch [1]
c) Dinner [5]
Comments: I hate lunch!

2) How much to you like cooking...
a) Breakfast [3]
b) Lunch [1]
c) Dinner [1]
Comments: Cooking dinner is a pain because it takes forever

3) How much do you like driving your car? [5]
Comments: I love my Chevy!

4) How much do you like these teams?
a) Red Sox [5]
b) NY Yankees [1]
c) Mariners [3]
d) St. Louis Cardinals [2]
Comments: The curse is over for the Sox!

5) How often do you watch these teams play?
a) Red Sox [5]
b) NY Yankees [5]
c) Mariners [3]
d) St. Louis Cardinals [4]
Comments: [None]
**************************************************************
An additional complication is that there are 4 types of surveys, where
each survey type includes only some of the total number of questions.

I am trying to make a separate table for each question type that
contains the response options, such as:
tblQType1
******
SurveyID
QuestionNum
Breakfast
Lunch
Dinner
comments
******

and have a separate list of questions:
tblQuestionList
************
QuestionNum
QuestionText
QType (1-4)
SurveyType1 (y/n)
SurveyType2 (y/n)
SurveyType3 (y/n)
SurveyType4 (y/n)
************
Is there a way to link the QType field in tblQuestionList to a
particular table, for example to tblQType1? The goal being to make a
form that looks something like the survey. Or maybe there is just a
better way of doing things altogether?

The only other thing I can think of is make each response possible for
each question and simply don't fill out the irrelevent ones.
i.e.
tblQuestionList
***************
QuestionNum
....
SurveyType4
Breakfast
Lunch
Dinner
Generic [ for the single sub-part questions]
Red Sox
NY Yankees
Mariners
St. Louis Cardinals
Comments
****************
This could work in my case, but it seems like it would quickly become
cumbersome for anything more complicated.

Sorry for the long post and thanks,
Katherine

 

[Bruce]

I'm not sure I get your full meaning, but I have a few ideas that might help
direct your planning. You need to ask yourself some questions about table
design and relationships. You should be able to describe your table's
function in a single sentence without using the word "and" (having said that,
a table can legitimately contain personal identifying information such as
name and address, but not name and favorite ball club). Clearly each survey
can contain many questions. If some questions appear on several different
surveys (and if you need to track such questions across all surveys in which
it appears), there could be a many-to-many relationship between surveys and
questions. It seems that at the least you will need a survey table and a
questions table. The questions table could contain, say, five multiple
choice fields and one comments field if there is some fairly low limit on the
number of choices per question. A better choice might be a responses table,
linked to the corresponding question.
It might look something like this:
tblSurvey
SurveyID (primary key)
SurveyTitle

tblQuestion
QuestionID (primary key)
SurveyID (foreign key)
QuestionText

tblResponses
ResponseID (primary key)
QuestionID (foreign key)
ResponseLetter
Comments

Comments could be in their own table instead of in tblResponses:
tblComments
CommentID (primary key)
QuestionID (foreign key)
Comment

You need to identify the primary key in table design mode, but the foreign
key is just a number field (assuming that primary key fields are autonumber,
which is probably a good idea). You don't identify foreign key fields as
such. Open the relationships window, and add the three tables (or four if
you are using tblComments). Drag the primary key fields onto the fields with
corresponding names in other tables. That will establish the relationship.
Once that is done, open the survey table. Add any necessary information to
the table, then click the plus sign (in Access 2000 or later). Enter the
questions.
Now you can think about forms. Create a survey form from tblSurvey (use
autoform to create a quick form with which you can experiment, or that you
can use as the basis for your own custom form). Do the same with the other
tables. With frmSurvey open in design view, drag the icon for frmQuestion
onto tblSurvey. The relationship between the corresponding tables has
already been established, so this will establish frmQuestion as a subform
within frmSurvey. Repeat with frmResponse and frmComment, dragging their
icons onto frmQuestion. Allow changes only in frmResponse and frmComment.
This should set up a form for a single survey question.
To add surveys, just add another survey to tblSurvey. Use the same
tblQuestion for all questions in all surveys, and the same tblResponse and
tblComment for all responses and comments for all questions in all surveys.
Remember that you do not need a separate tblQuestions for each survey, nor a
separate tblResponse for each question. The relationships make all of this
work.
No doubt there are more details. For instance, is there any connection
between the different surveys, are the survey takers identified in any way,
etc.? Get the relationships working, then work on other requirements. Good
luck.

Hi,

I'm trying to write a database to store surveys. The problem is that
different questions have different numbers of sub-parts. For each
sub-part, the person gives a response from 1 to 5 as well as comments
for the whole question. For example (not the real survey)
**************************************************************
1 = don't like; 2 = could be worse; 3 = ok; 4 = pretty well; 5 = a lot!
1) How much do you like eating...
a) Breakfast [4]
b) Lunch [1]
c) Dinner [5]
Comments: I hate lunch!

2) How much to you like cooking...
a) Breakfast [3]
b) Lunch [1]
c) Dinner [1]
Comments: Cooking dinner is a pain because it takes forever

3) How much do you like driving your car? [5]
Comments: I love my Chevy!

4) How much do you like these teams?
a) Red Sox [5]
b) NY Yankees [1]
c) Mariners [3]
d) St. Louis Cardinals [2]
Comments: The curse is over for the Sox!

5) How often do you watch these teams play?
a) Red Sox [5]
b) NY Yankees [5]
c) Mariners [3]
d) St. Louis Cardinals [4]
Comments: [None]
**************************************************************
An additional complication is that there are 4 types of surveys, where
each survey type includes only some of the total number of questions.

I am trying to make a separate table for each question type that
contains the response options, such as:
tblQType1
******
SurveyID
QuestionNum
Breakfast
Lunch
Dinner
comments
******

and have a separate list of questions:
tblQuestionList
************
QuestionNum
QuestionText
QType (1-4)
SurveyType1 (y/n)
SurveyType2 (y/n)
SurveyType3 (y/n)
SurveyType4 (y/n)
************
Is there a way to link the QType field in tblQuestionList to a
particular table, for example to tblQType1? The goal being to make a
form that looks something like the survey. Or maybe there is just a
better way of doing things altogether?

The only other thing I can think of is make each response possible for
each question and simply don't fill out the irrelevent ones.
i.e.
tblQuestionList
***************
QuestionNum
....
SurveyType4
Breakfast
Lunch
Dinner
Generic [ for the single sub-part questions]
Red Sox
NY Yankees
Mariners
St. Louis Cardinals
Comments
****************
This could work in my case, but it seems like it would quickly become
cumbersome for anything more complicated.

Sorry for the long post and thanks,
Katherine