Survey Answers

[dee]

I have been working on a survey database and between AYS, advice here,
research and reading books, it is coming along quite nicely.

tblQuestionnaires
QstnaireID (autonumber PK)
QstnaireName
QstnaireDescription

tblQuestions
QstnID (autonumber PK)
QstnaireID (FK from tblQuestionnaires)
SectionNo
SectionName
SubsectionName
QstnPrefix (these differ depending on the qstnaire)
QstnNo
QuestionText

tblAnswers
QstnID (FK from tlbQuestions)
Answer (Both of these fields comprise the PK)

tblResponses
PptNo (combine with QstnID for PK)
QstnID (FK from tlbQuestions)
Answer

I also have tblParticipants that contains detailed particpant info,
including name, dob, language, etc., etc. and tblInterviewer that contains
the interviewer code.

My question, for which I have seen many different approaches, but still I'm
not clear on is:

How can I construct a form that lets me:
- Use a check box for Y/N
- A combo-box that allows one response only from the list of answers
- A method to allow a respondant to select multiple answers
- A method where the user may select from a list and/or write their own
response

And how does this relate back to my tables to populate them with the correct
responses?

 

[Amy Blankenship]

I have been working on a survey database and between AYS, advice here,
research and reading books, it is coming along quite nicely.

tblQuestionnaires
QstnaireID (autonumber PK)
QstnaireName
QstnaireDescription

tblQuestions
QstnID (autonumber PK)
QstnaireID (FK from tblQuestionnaires)
SectionNo
SectionName
SubsectionName
QstnPrefix (these differ depending on the qstnaire)
QstnNo
QuestionText

Having SectionNo, SectionName, and SubSectionName in this table is not
properly normalized. Instead, you need a Section table, a Subsection table
(with a reference to the Section table to tell you which Section it is part
of), and then the reference to SubsectionID would go into this table.

tblAnswers
QstnID (FK from tlbQuestions)
Answer (Both of these fields comprise the PK)

tblResponses
PptNo (combine with QstnID for PK)
QstnID (FK from tlbQuestions)
Answer

I also have tblParticipants that contains detailed particpant info,
including name, dob, language, etc., etc. and tblInterviewer that
contains
the interviewer code.

My question, for which I have seen many different approaches, but still
I'm
not clear on is:

How can I construct a form that lets me:
- Use a check box for Y/N
- A combo-box that allows one response only from the list of answers
- A method to allow a respondant to select multiple answers
- A method where the user may select from a list and/or write their own
response

And how does this relate back to my tables to populate them with the
correct
responses?

These tasks are not easy. Google on frustrated join for some potential
approaches.

HTH;

Amy

 

[dee]

Hi there,

Yes, I know the SectionName, etc. should be further normalized and will do
so once the data is transferred in from Excel.

Thanks for the suggestion.

 

[Beetle]

If you know that some of your tables need to restructured for better
normalization, you should do it now, not later. If you start creating other
objects (forms, reports, queries) based on your current table structure, and
then you change that structure, your going to have to redesign all the other
objects also.

 

[dee]

Yes, I know that. I'm just testing some things out right now.

I'm really looking for how to solve my answers problem right now, so any
help in that area would be greatly appreciated.

 

[Michael Gramelspacher]

Yes, I know that. I'm just testing some things out right now.

I'm really looking for how to solve my answers problem right now, so any
help in that area would be greatly appreciated.

I responded previously, but have now changed my program somewhat.
http://www.psci.net/gramelsp/temp/Survey_Practice_2.zip