Query (Counts) and Report Question

[dllivermore]

Hello all,

This is my first time posting on an Access forum. I have basically been teaching myself Access the past year for work related reasons. I am finally stumped on something and can't seem to find an answer. I'll give you a run down of what I am doing.

For groups we are doing, we are conducting a small survey before and after the group, asking the same questions each time. I developed one table and a form for that table to enter the results from these surveys. The before and after surveys are two separate sheets and names are optional. When doing the data entry, a new record is created for each sheet. We enter the basic information from the class, then I have a drop down to select before or after, then a field for the question, then a drop down for the response since that will always remain the same. At the most we will be asking five questions.

I have no problem creating a query and report based on the individual records for the persons who attended. However, I am having the hardest time creating a query/report giving me totals for all of the answers given. Basically, I want my query/report to state each question asked, and the total answers given before and after. The query/report would be run based on the date of the class as well.

The closest I was able to get was to have a query for each question, before and after. So if we ask five questions, I have a total of ten queries. I then generate a report that has subreports for each of these queries. Since a date is used, it requires entering the date in multiple times. This won't even work if we have two groups on the same day.

I know this is the wrong way to do it. While I can make a report using the report wizard to show the results of one of the questions, for whatever reason the report wizard won't do anything beyond the first question. It just repeats the numbers.

Obviously I am doing something wrong. I'd appreciate any advice on this. My experience in SQL is very limited, so using non-SQL methods would be best.

Thank you.

[aytee111]

What you need is a Totals query - at the top right in query design there is an E-shaped icon, click on that. It will add another line to the query for groupings. You will need to group by question, before/after and date. For the answers you will use Count.

[dllivermore]

What you need is a Totals query - at the top right in query design there is an E-shaped icon, click on that. It will add another line to the query for groupings. You will need to group by question, before/after and date. For the answers you will use Count.

That's what I previously tried. The problem is the count remains the same for all of the questions. It just copies that data from the first question. I have tried queries, pivot tables, and pivot charts trying to get it to work. But I can only show the results from one question at a time. The results can't be shown for all of the questions.

Thank you.

[aytee111]

Post the SQL for this query.

[dllivermore]

Post the SQL for this query.

SELECT [Pre & Post Table].Group, [Pre & Post Table].[Date of Class], [Pre & Post Table].[Facilitator #1], [Pre & Post Table].[Facilitator #2], [Pre & Post Table].[Pre or Post], [Pre & Post Table].[Question #1], [Pre & Post Table].[Response #1], [Pre & Post Table].[Question #2], [Pre & Post Table].[Response #2], [Pre & Post Table].[Question #3], [Pre & Post Table].[Response #3], [Pre & Post Table].[Question #4], [Pre & Post Table].[Response #4], [Pre & Post Table].[Question #5], [Pre & Post Table].[Response #5], Count([Pre & Post Table].[Response #1]) AS [CountOfResponse #1], Count([Pre & Post Table].[Response #2]) AS [CountOfResponse #2], Count([Pre & Post Table].[Response #3]) AS [CountOfResponse #3], Count([Pre & Post Table].[Response #4]) AS [CountOfResponse #4], Count([Pre & Post Table].[Response #5]) AS [CountOfResponse #5]
FROM [Pre & Post Table]
GROUP BY [Pre & Post Table].Group, [Pre & Post Table].[Date of Class], [Pre & Post Table].[Facilitator #1], [Pre & Post Table].[Facilitator #2], [Pre & Post Table].[Pre or Post], [Pre & Post Table].[Question #1], [Pre & Post Table].[Response #1], [Pre & Post Table].[Question #2], [Pre & Post Table].[Response #2], [Pre & Post Table].[Question #3], [Pre & Post Table].[Response #3], [Pre & Post Table].[Question #4], [Pre & Post Table].[Response #4], [Pre & Post Table].[Question #5], [Pre & Post Table].[Response #5]
HAVING ((([Pre & Post Table].[Date of Class]) Between [StartDate] And [EndDate]));

[aytee111]

It looks like these are Excel worksheets put into Access - all data going across instead of down. I had assumed Access tables and so my instructions will not work with this.

To turn this into Access, your tables need to look something like this:
Question_tbl
questionID
question

Response_tbl
responseID
questionID
pre_post
response

This is an example, I have not studied your requirements further. With this type of table layout queries and reports are straightforward. No wonder the wizard was unable to help you.