Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2012
    Posts
    2

    Answered: Count 2-Part Ranked Answers (1-5) In 23 Question Survey

    Good afternoon; thank you for taking a look at my issue.

    I was asked to create a survey database that has 23 two-part questions (the first part is how the user scores the Importance of the question, and the second is how well is the question Met by the unit supervisor): Importance (1-5) and Met (1-5). The form is the question, a combobox for the user to select 1-5 for Importance, then 1-5 for Met, then a text box for the user to enter their comments. All fields are required for each question (see attachment below):

    Click image for larger version. 

Name:	Survey Sample.PNG 
Views:	2 
Size:	2.8 KB 
ID:	16512

    The stakeholder came to me the other day asking me for a report that would count how many responses were '5', and how many were '4', and how many were '3', etc, for both parts of each of the questions. The solution I initially devised was to fashion a query that would nest separate queries to count the 5's of the first part of the first question, then the 4's of the first part of the first question, etc, all into a single query. That query quickly became far, far too large to run as that would end up being 230 separate queries within a single query, and Access didn't allow me to do that. So then I decided to create 23 separate queries using this bit of code:

    Code:
    SELECT (SELECT COUNT(*) from qryReportCard WHERE qryReportCard.Q1Important=5) AS Q1Importance5s, (SELECT COUNT(*) from qryReportCard WHERE qryReportCard.Q1Important=4) AS Q1Importance4s, (SELECT COUNT(*) from qryReportCard WHERE qryReportCard.Q1Important=3) AS Q1Importance3s, (SELECT COUNT(*) from qryReportCard WHERE qryReportCard.Q1Important=2) AS Q1Importance2s, (SELECT COUNT(*) from qryReportCard WHERE qryReportCard.Q1Important=1) AS Q1Importance1s, (SELECT COUNT(*) from qryReportCard WHERE qryReportCard.Q1WellMet=5) AS Q1WellMet5s, (SELECT COUNT(*) from qryReportCard WHERE qryReportCard.Q1WellMet=4) AS Q1WellMet4s, (SELECT COUNT(*) from qryReportCard WHERE qryReportCard.Q1WellMet=3) AS Q1WellMet3s, (SELECT COUNT(*) from qryReportCard WHERE qryReportCard.Q1WellMet=2) AS Q1WellMet2s, (SELECT COUNT(*) from qryReportCard WHERE qryReportCard.Q1WellMet=1) AS Q1WellMet1s, qryReportCard.Supervisor, qryReportCard.SurveyMonth, qryReportCard.SurveyYear
    FROM qryReportCard
    GROUP BY qryReportCard.Supervisor, qryReportCard.SurveyMonth, qryReportCard.SurveyYear;
    Each of those queries work wonderfully individually, but I then placed each into it's own subreport and assembled all of the subreports onto a single report to present the results of the first 16 questions per the stakeholder's request (the last 7 questions would be in a separate report). When I tried to run the report I'm presented with the error "Cannot open any more databases."

    I understand that the error is telling me that I've got more than 2048 connections to the db (I reduced the number of subreports to 10 in/on a single report and it runs correctly; it's quite slow, but it runs), which is too many for Access to process what I want. Would some kind soul care to suggest a different manner in which I can write the query or queries to accomplish what the stakeholder is requesting? I'd really appreciate your help.

  2. Best Answer
    Posted by healdem

    "Preprocess the survey results in a query, then summarise.
    So query 1 is something like
    Code:
     select iif (q1 = 0,1,0) as Q1_0, iif (q1 = 1,1,0) as Q1_1 from mytable
    Add as many questions you need subject to the 250 or so limit as say myquery

    Then summarise in another query
    Select sum(q1_0) as No_Q1_0, sum (q1_1) as No_Q1_1 from myquery

    If you need all values in a single report, break the report up into sub reports"


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Preprocess the survey results in a query, then summarise.
    So query 1 is something like
    Code:
     select iif (q1 = 0,1,0) as Q1_0, iif (q1 = 1,1,0) as Q1_1 from mytable
    Add as many questions you need subject to the 250 or so limit as say myquery

    Then summarise in another query
    Select sum(q1_0) as No_Q1_0, sum (q1_1) as No_Q1_1 from myquery

    If you need all values in a single report, break the report up into sub reports
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Aug 2012
    Posts
    2
    Perfect! Thanks for the simple, elegant answer healdem.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •