Suvey Database Tables

[dee]

I have received great advice regarding my database. Am reading Database
Design for Mere Mortals and have also downloaded "At Your Survey" database,
both of which have been extremely useful.

I am in the process of constructing my tables, which, so far are comprised
of (in general):

tblParticipants
Id PK, Participant Number, Last, First, Address, Phone, etc.

tblSurveys
ID PK, survey name, survey code

tblInterviewers
ID PK, name of interviewer, their code

tblFamilyRelationships
ID PK, relationship number, relationship (father, mother, brother, cousin,
aunt, etc. in relationship with the head of household)

I now want to create a table that contains the questions and another that
contains the possible responses to the questions.

Each question is denoted by the survey code, followed by Q1, Q2, etc. That
is OK from what I have so far created...

The answers are numbered: 1 - Yes, 2- No, etc. However, answers may also
be 1 - male, 2 - female, etc. In other words, 1, 2, 3, 4, 5, 6, etc. of
possible answers that can be chosen are not always the same answers.
However, I do have answers 50, 51, 52 and 53 that are always the same (n/a,
Doesn't know, etc.)

I am OK with inputting the questions, showing the questions numbers by
displaying the corresponding questionnaire code and question number.

However, how should I approach the content of the Responses table?

I hope this is somewhat clear!

 

[Dale Fye]

dee,

In a survey database that I use, we have a responses table that has a
ResponseID, and the text of the response. Then we have a QuestionResponses
table that has a QuestionID, ResponseID, and SortOrder fields, so that we can
assign specific responses from the Response table to each question, and
identify the sequence of the responses.

HTH
Dale

 

[dee]

Hi Dale - thanks for your response.

I'm not clear on which table is which... which is the table that contains
the possible answers to the questions and which table contains the actual
answer that was chosen?

Can you also explain in more detail the sort field?

Thanks!

 

[Dale Fye]

Neither of the ones I talked about yesterday. Those two tables reflect all
the possible responses to all questions (tbl_Responses) and the available
options for each question (tbl_QuestionResponses).

The table that stores the results of the actual questionnaire (or survey) is
tbl_QuestionnaireResponses, and it contains fields(UserQuestionnaireID,
QuestionID, and ResponseID).

In this particular application, the same user may be given the same survey
multiple times so we have another table (tbl_UserQuestionnaires) that
contains fields (UserQuestionnaireID, UserID, QuestionnaireID,
CompletionDate, and a couple of other fields).

HTH
Dale

 

[dee]

Hi Dale,

In terms of relating tables... I am starting that now.

I have a tblQuestionnaires (there are many) and a tblRespondents .

Each respondent will answer most or all questionnaires.

I am looking at the Survey database by Duane Hookom, which shows a
one-to-many relationship between the questionnaire (one) and respondents
(many).

Any thoughts?

 

[dee]

Hi Dale,

I'm using AYS and am encountering a problem.

In the form I'm using to input the questions, I want to sort by 2 levels:
MainSection, then QuestionNo.

I know you had mentioned sort fields. Can you give me some more detailed
information on how to do this?

Many thanks