Using MS Access to track survey results

Factual Questions
1

Hello and thanks for any help you can offer. I am trying to design a customer survey for my wife’s store. I would like to use MS Access to track and evaluate the results. When the survey is handed out, we hope to have about 200 returned, so I need an easy way to enter and eval the data.

I have been reading several examples about different tables. So far I have created a table for questions, a table for possible answers, a table for responses. My problem is I’m not sure how to list the information.

Here is what I have:

tblQuestions
QuestionID (autonumber) - primary key
Question number (number) that I type in
Question Test (text) - type in question text

tblPossibleAnswers
AnswerID (autonumber) - primary key
QuestionID (number) - I type in, to match question (can I make this autofill)
Choices (txt) - I type in one answer per box

tblResponses
ResponseID (autonumber) - primary key
SurveyID (number) - I type in the number from the paper survey
QuestionID (number) - I type in number that matches question
also started a column for each question and using drop down menu with link to choices, but it lists every possible answer

I think I’m going in the right direction, just not sure how to make this all come together. THANK YOU FOR YOUR HELP

2

I am assuming the simplest case where your database holds info about one particular survey, and the questions are all on this one survey. The generalized answer is more complex (where you track many surveys, and the questions can be used in more than one survey. Also, I think the response to a question is limited to one choice, correct? i.e. they can’t pick “1” *and *“2”… again that simplifies the problem.

Some nitpicking:

I am ambivalent on artificial keys, but avoid them in situations where you have an “Identifying” relationship. That is a Parent-Child where, if the Parent disappears, the child records have no meaning and should disappear as well. So, tblPossibleAnswers should be:

tblPossibleAnswers
(pk)QuestionID (number) - foreign key to tblQuestion
(pk)AnswerNo - e.g. 1
AnswerText - e.g. “I am under 21 years of age”

The simple answer to your main question “how do I bring this all together” is to use a form and subform to list and enter the Q’s and A’s. See http://office.microsoft.com/en-us/access-help/create-a-form-that-contains-a-subform-a-one-to-many-form-HA010098674.aspx

For the responses, you are missing a table “tblSurveyResponses” that shows who filled out the survey, i.e. name, address, gender, age, etc. If this info is missing, because the surveys are anonymous, then your one table tblResponses will do.

tbl(Survey)Responses(Answers) needs a form. It will have list boxes for QuestionID and the AnswerID. This form will be your greatest challenge. The list boxes hold the ID’s but display the description of the Q and A. Refer http://office.microsoft.com/en-ca/access-help/create-a-list-box-combo-box-or-drop-down-list-box-HP005240581.aspx

There is more to this, I wonder if there is an example database out there on the interweb thing?