Reporting Survey Questions and Answers

[Blkblts]

I am using Access 2002. I have created queries in hopefully the normalized form. tblSurvey, tblQuestion, tblAnswers, tblResponseoptions. I have also created reports. Now I have been told the user wants to pick and choose which question go on the report. Gee Wiz.. Can anyone point me in the right direction on this. I literally have to create 60 queries for about a 20 question survey just to get the data the way they want it. An example is not just the answers, but Only report favorable answers. Any suggestions welcome. Just covering my bases incase my answer is no frigging way...

Thanks in advance.

 

[Sergeant]

So can you add a yes/no field to your question table? If you are using 60 queries for this, there's a problem.

Have you read this post?

 

[Rich]

The answer is almost certainly to construct the SQL of the Report from selections made in a multiselect listbox, searching here for posts on that topic should point you in the right direction

 

[Blkblts]

I agree 60 queries is alot. All I receive is an .xls file that has 20 Answers for a user in a single row. The columns are the Questions. Some of the answers are favorable. Such as Is you doctor nice. Yes would be more favorable then NO. So I can only put a total of who said YES in the report. Another Question is Gender. Answer is Male or Female. (This is the Column heading for my report. I have to have the following information and format.

Report 1

Question Female Male
Is your dr. nice? 122 100
Rate your dr. knowledge? 100 110
(Favorable answer = Excellent)

Report 2
Q1 Is your dr. nice? Yes No TTL
222 400 622

Q2 Rate your dr's knowledge Excellent Good Poor TTL
210 300 100 610


My querys are so many because I have to first find all the favorable answers for each question. Then find out how many are male and how many are female. ect. Hope this answers your question

 

[Blkblts]

Rich,
My query that is behind the report is a union query of all my queries. By using the multiselct box then when they clicked a question I would build the SQL of
Select * from Q1_TTL
Union
Select * from Q2_TTL
Union
Select * from Q5_TTL
Union

And so on until they were finished with their selections.

How would I delete the final UNION that would be written?