Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Access report – analysis of ratings

Posted on 2012-09-06
Medium Priority
Last Modified: 2012-09-10
I need to produce a report which produces an analysis grid for the results of a series of 8 option fields.  To explain:
I have 8 fields to provide different ratings, such as “do you feel happier?”
These fields use option buttons on a form which set the database field to a value (1 to 5)
So I have 8 fields in a record that can each be set to a numeric value from 1 to 5.

I need a report to analyse this information in a grid such as this:

Question 1       Excellent [count]      Good [count]               OK [count]   etc
Question 2    Very much [count]    A little [count]     Not at all [count]   etc

I have never produced a report like this before.  I could do it by creating a report containing subreports for each count.  That would require 40 subreports with 40 queries and seems a bit clumsy.  Is there a better approach to take before I start?

Hope this is all clear enough.


Question by:rltomalin
  • 2
LVL 61

Accepted Solution

mbizup earned 1600 total points
ID: 38371535
Try creating a subreport for each question - so 8 subreports, one for each question, each having a one line summary of the results.

The recordsource queries for the subforms would look something like this:

For question 1:

SELECT SUM(ABS([Question1] = 1)) AS [Number of Poor], SUM(ABS([Question1] = 2)) AS [Number of OK], SUM(ABS([Question1] = 3)) AS [Number of Better], SUM(ABS([Question1] = 4)) AS [Number of Good], SUM(ABS([Question1] = 5)) AS [Number of Excellent]
FROM YourTable

For question 2:

SELECT SUM(ABS([Question2] = 1)) AS [Number of Poor], SUM(ABS([Question2] = 2)) AS [Number of OK], SUM(ABS([Question2] = 3)) AS [Number of Better], SUM(ABS([Question2] = 4)) AS [Number of Good], SUM(ABS([Question2] = 5)) AS [Number of Excellent]
FROM YourTable


Author Comment

ID: 38371710
Thanks mbizup

I am not able to get onto this now until next week.  This certainly looks a bit simpler.

I will take a look at this and any other suggestions that come in next week.

Best regards

LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 400 total points
ID: 38372485
Since all of the question responses range from 1 to 5, you can actually use a crosstab to produce this:

TRANSFORM Count(z.[SurveyID]) AS CountOfSurveyID
SELECT z.[Question], Count(z.[SurveyID]) AS [Total Of SurveyID]
(SELECT SurveyID, "Q1" AS Question, Q1 AS Answer
FROM Surveys
SELECT SurveyID, "Q2" AS Question, Q2 AS Answer
FROM Surveys
SELECT SurveyID, "Q3" AS Question, Q3 AS Answer
FROM Surveys
SELECT SurveyID, "Q4" AS Question, Q4 AS Answer
FROM Surveys
SELECT SurveyID, "Q5" AS Question, Q5 AS Answer
FROM Surveys
SELECT SurveyID, "Q6" AS Question, Q6 AS Answer
FROM Surveys
SELECT SurveyID, "Q7" AS Question, Q7 AS Answer
FROM Surveys
UNION ALL SELECT SurveyID, "Q8" AS Question, Q8 AS Answer
FROM Surveys) AS z
GROUP BY z.[Question]
PIVOT z.[Answer];

Open in new window

Note that this would have been easier if your question responses had been saved as a normalized table.  Because your source data is denormalized, I had to use a UNION subquery to normalize it before it could go into the crosstab.

See the attached database for a working example.

Author Closing Comment

ID: 38382807
Thank you for the two solutions.  I have awarded some points to matthewspatrick.  I suspect his is maybe the more "correct" way of doing this.  However, as a bit of a newbie, I must say that I follow the solution given by mbizup better and that is what I will use.

Best regards


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question