Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2002
    Posts
    35

    Unanswered: displaying output of several count queries on a form or report

    Hello,
    I am looking for some assistant with Access 2000 reports or forms.

    I have a survey table that has questions (q1 through q19) and the results are stored as numbers. I know how to write the SQL to count the totals for each number. For example, q1 has 5 possible numbers (not including the null option which I am disregarding for now) so I have 5 queries for this question which look like this and give me the results I desire:

    Code:
    SELECT count([tblFinalSurveyResults].[q1]) AS Q1_Agree
    FROM tblFinalSurveyResults WHERE q1=2;

    What I need to do is create a report or form that will display the results of all these queries but when I try to create either, I get the following message

    "You have chosen fields from record sources which the wizard cannot connect. You may have chosen fields from a table and from a query based on that table. If so, try choosing fields from only the table or query"

    Each of the queries to count are separate queries (so I have 40 queries right now for questions 1 thru 8 which all have 5 options). No joins on other tables, and the table in question is a very simple, non related (via the relationship) to anything.




    I have browsed through the archives and have found some similar situations to what I am trying to do but am not clear about to actually display the output of my queries which is what I seem to need help with.


    OR perhaps I should instead be using one of the built in functions via the expression builder instead
    (count<<expr>>) and if so, what would the expression be?? I have tried

    Code:
     
    count([q1]=2)
    And I get different statistical results than my queries give me (it seem to just count the number of records that have an answer, not the number of records that are a number '2')


    Any advise is much appreciated,

    Joyce E. Evans

  2. #2
    Join Date
    Nov 2002
    Posts
    150

    Re: displaying output of several count queries on a form or report

    Help me understand what result you are trying to accomplish. Each question have 5 different answers (6 counting 'null'). If 5 people take the survey, do you want counts like this:

    Question# Answer Count
    1 null 0
    1 1 2
    1 2 1
    1 3 0
    1 4 1
    1 5 1

    If so, write your query like this:

    SELECT Q1 as QuestionNumber, ANSWER, COUNT(1)
    FROM TABLE
    GROUP BY Q1, ANSWER
    UNION
    SELECT Q2 as QuestionNumber, ANSWER, COUNT(1)
    FROM TABLE
    GROUP BY Q2, ANSWER
    UNION
    etc.... until you have all 19.

  3. #3
    Join Date
    Nov 2002
    Posts
    35
    Thanks for the quick reply!

    This is what I want:

    Strongly Agree-1 Agree-2 Neither-3 Disagree-4 Strongly Disagree-25

    Q1 500 278 5 2 0
    Q2 450 258 15 12 3
    Q3 485 298 9 19 13

    My queries give me these values, I just need to figure out how to display them on a report or a form. Does that make sense??

    ~ Joyce

  4. #4
    Join Date
    Nov 2002
    Posts
    150
    Originally posted by perlgurl
    Thanks for the quick reply!

    This is what I want:

    Strongly Agree-1 Agree-2 Neither-3 Disagree-4 Strongly Disagree-25

    Q1 500 278 5 2 0
    Q2 450 258 15 12 3
    Q3 485 298 9 19 13

    My queries give me these values, I just need to figure out how to display them on a report or a form. Does that make sense??

    ~ Joyce
    So for Q1, 500 people chose answer 1 (Strongly agree)?

  5. #5
    Join Date
    Nov 2002
    Posts
    35
    Yes, and that is what I am trying to get at to output on the form (or report). Eventually that number will turn into a percentage with further calculations (so 89% said they strongly agree) but I can't get the 500 to show up on anything.

    ~Joyce

  6. #6
    Join Date
    Nov 2002
    Posts
    150
    Originally posted by perlgurl
    Yes, and that is what I am trying to get at to output on the form (or report). Eventually that number will turn into a percentage with further calculations (so 89% said they strongly agree) but I can't get the 500 to show up on anything.

    ~Joyce
    What is your table structure? Is it a survery number (key) with 19 question fields? If so, use the report wizard to create totals/sums for question field.

  7. #7
    Join Date
    Nov 2002
    Posts
    35
    Thanks again,

    That is exactly my structure, surveyID, and q1 thru q19, but the report wizard would only let me do grouping or sorting on four field, I have 19 question fields.


    I just went back to the wizard and I see what step the sum/avg/min/max is on ("summary options" tab on step 3), but the wizard does not seem give me the "summary options" tab unless I group by (step 2) something and I have tried grouping by all the permutation I can think of (even though they do not make sense) and I am not getting the results I need.

    Help??
    ~ Joyce

  8. #8
    Join Date
    Nov 2002
    Posts
    150
    create a query and put the 19 question fields in the grid. Click on the "Totals" menu button and select what type of total you want for each field. Then use this query as the record source for your report.

Posting Permissions

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