Tables for a Questionnaire with different response data types?

A

annem

I have a table for the questions (54 of them) with one record per question
and the QuestID as the primary key. I have a table for the responses with a
record for each facility responding. That table does not include the
QuestID. My problem is creating a query that pulls in the QuestId and
matches it with the appropriate response for each facility. I reviewed the
Knowledge Base but only found suggestions when the response types are all a
'yes/no' data type. I have various data types. Any advice? Thanks in
advance, Anne
 
D

Douglas J. Steele

If your response table doesn't have the QuestID in it, how do you know the
question to which they were responding?
 
A

annem

I'm sorry, I 'misspoke'. It does have the QuestID, but not the text of the
question. It has a field with "Q1" and a data type appropriate for the
question whose QuestID is "Q1". I want to pull the Question itself (which is
in a field called "Question" in the record of each QuestID.
 
D

Douglas J. Steele

Are you saying that your response table has 54 fields named Q1, Q2, Q3 etc.?
If so, your table has not been normalized: that's called a repeating group,
and is almost always a bad idea.

Instead, you should have 54 rows corresponding to each set of answers. Yes,
if different questions can have different answer types, this can be a little
messy. One approach is to have a field for possible boolean answers, one for
possible numeric answers and one for possible text answers, and have an
indicator in the Question table as to which one you should be using.
 
A

adsl

annem said:
I'm sorry, I 'misspoke'. It does have the QuestID, but not the text of
the
question. It has a field with "Q1" and a data type appropriate for the
question whose QuestID is "Q1". I want to pull the Question itself (which
is
in a field called "Question" in the record of each QuestID.
Click to expand...
 
A

adsl

Douglas J. Steele said:
Are you saying that your response table has 54 fields named Q1, Q2, Q3
etc.? If so, your table has not been normalized: that's called a repeating
group, and is almost always a bad idea.

Instead, you should have 54 rows corresponding to each set of answers.
Yes, if different questions can have different answer types, this can be a
little messy. One approach is to have a field for possible boolean
answers, one for possible numeric answers and one for possible text
answers, and have an indicator in the Question table as to which one you
should be using.
Click to expand...
 
A

adsl

Douglas J. Steele said:
Are you saying that your response table has 54 fields named Q1, Q2, Q3
etc.? If so, your table has not been normalized: that's called a repeating
group, and is almost always a bad idea.

Instead, you should have 54 rows corresponding to each set of answers.
Yes, if different questions can have different answer types, this can be a
little messy. One approach is to have a field for possible boolean
answers, one for possible numeric answers and one for possible text
answers, and have an indicator in the Question table as to which one you
should be using.
Click to expand...
 
A

adsl

annem said:
I'm sorry, I 'misspoke'. It does have the QuestID, but not the text of
the
question. It has a field with "Q1" and a data type appropriate for the
question whose QuestID is "Q1". I want to pull the Question itself (which
is
in a field called "Question" in the record of each QuestID.
Click to expand...
 
A

adsl

Douglas J. Steele said:
Are you saying that your response table has 54 fields named Q1, Q2, Q3
etc.? If so, your table has not been normalized: that's called a repeating
group, and is almost always a bad idea.

Instead, you should have 54 rows corresponding to each set of answers.
Yes, if different questions can have different answer types, this can be a
little messy. One approach is to have a field for possible boolean
answers, one for possible numeric answers and one for possible text
answers, and have an indicator in the Question table as to which one you
should be using.
Click to expand...
 
Top