Report writing (Access 97)

Topic

New Reply

I have a database that is being used to collect survey data. No problem….well…sorta. Each survey question can have three answers and there are drop-down list of the 10 choices. A user could answer one question with one answer and someone else may pick three from the list. I have set up the table with 3 fields.

How can I generate a report with a tally for each choice per question? Does that make any sense?

Thanks,
Deborah

Reply | Quote

Replies

In my survey applications there are 3 tables: tblSurvey (Survey Header information), tblQuestion (the Survey questions) and tblAnswer (holds the tblSurvey.SurveyID, the tblQuestion.QuestionID and the respondent’s answer). In order to tally responses, I count and calculate on tblAnswer for a particular QuestionID.

I believe that even though your questions have a maximum of 3 possible answers, that structure will still work. That is; the same Survey will not have more than one identical response to the same question.

I would not recommend that you structure your Answer table with 3 fields for the 3 answers as you have suggested. It is not normalized and will be prove difficult to tally answers when they could reside in more than one field.

Reply | Quote

Thanks for the reply. You know that Access is like everything else – if you don’t use it – you will lose it. I have currently switched from the computer training field to corporate training and I feel like I don’t remember anything. Well…I understand your logic, however, I am unsure about what is in your question table. I know questions but how is it built. Is it possible to send me a sample of your survey database or of the relationships. I am more of a visual learner and I need to see it to get a clearer idea of what to do.

Thanks for all your assistance,

Deborah

Reply | Quote

I had previously posted a sample survey structure in This Post.
The key on the tblResponse (I referred to it as tblAnswer before I saw this sample) needs to be expanded for your purpose because the same QuestionID can have more than 1 record per SurveyID. Possibly all three fields?

Reply | Quote

I can not preview the attachment because I have access 97.

I will view the old post information and see what I can come up with.

Thanks,
Deborah

Reply | Quote

If someone can tell me how to include a picture of the Db’s relationship window, I think that might help you understand how it is structured.

Reply | Quote

You can do a screen print and paste in Word and then attach. I have not tried to screen print and paste in the forum.

Looking forward to seeing the printout.

Deborah

Reply | Quote

I attached it as a zipped Word document which contains the Db Relationships window.
HTH

Reply | Quote

I know you must think I am as dumb as dirt but I am having difficulty with the concept you gave me. I went home and opened the database and converted it to an earlier version so I can at least look at it. I added answer 1, answer 2 and answer 3 to the tblresponse table. Is this correct? Do I put all my questions in the tblquestion table?

When you generate a form so the user can add data, did you create a query of all fields? I have drop-down list for the user to select an answer – is this still going to work?

I can not see the whole picture and I am having problems with the form.

HELP!!! I don’t think it is only the state of SC in a drought – my mind is had dried up!

Thanks,
Deborah

Reply | Quote

The response table should hold the actual answers selected, not your proposed answers. To use comboboxes on your form you would need a table holding the ProposedAnswers along with something like the Question ID so that you could query out the proposed answers by question. The selected value would be the one inserted into the Reponses table.

Reply | Quote

Not at all … you’re just trying to understand.

I would not recommend that you change the structure of tblResponse that way (Answer1, Answer2, Answer3) because that is not normalized. I am saying that if SurveyID 1 chooses 3 Answers (let’s say 25, 35 and 45) for Question 6. then tblResponse would have 3 records like this:

SurveyID	QuestionID	Answer
1	             6	          25
1	             6	          35
1	             6	          45

Then a respondent can choose as many answers as you permit per question and your statistics can be calculated from tblResponse per QuestionID.

I do put all the questions in a tblQuestion like this:

QuestionID	QuestionText
1	        text of the first question
2	        text of the second question
3	        text of the third question

The data entry form is unbound, (yes, you can use dropdown boxes) and I use code to write the 3 fields (SurveyID, QuestionID and Answer) to tblResponse. A new record is written for each Answer.

Although it takes a while to code the unbound data entry and edit forms, it has served me well through numerous types of survey applications. Is that clearer?

Reply | Quote

The way you have set it up, you could run a unioin query that first got one field, then the second then the third. You could then build a grouping query on that.

Another option is to move the answers to the questions to a separate table from the questions, (with a 1 – many relationship) then you could store any number of response in the same field.

Reply | Quote