Access report – analysis of ratings

Posted on 2012-09-06
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
    LVL 61

    Accepted Solution

    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

    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 92

    Assisted Solution

    by:Patrick Matthews
    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

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now