locked
Summary Question RRS feed

  • Question

  • Is this possible .....  I have a query that counts record based on if an answer to a question is a Y or N.  So the query counts each response and then groups the answer by the response (either Y or N).  So my result is 2 rows - on has the count of Y and the other the count of N.  It has two fields, one is the count field and the other the group field.  Is it possible in a simple count query to add a column that calculates the percentage?  So I am thinking a third field that on the Y group record, I get the percentage of Y for all answers and on the N group record, I get the percentage that was N. 
    Thursday, November 19, 2015 2:15 PM

Answers

  • To see the Percentage field as percent vs. decimal fraction, you can open the query in design view and open the Properties dialog.  With the Percentage field selected, set the Format property to Percent.  You do not need to do any additional multiplication or division from what I originally provided to you.
    • Marked as answer by seebert Thursday, November 19, 2015 3:59 PM
    Thursday, November 19, 2015 3:23 PM

All replies

  • The following example should get you on your way:

    SELECT [QuestionAnswer].Answer, Count([QuestionAnswer].Answer) AS CountOfAnswer, Count(Answer) / (SELECT Count(QuestionAnswer.Answer) AS CountOfAnswer FROM QuestionAnswer) AS Percentage
    FROM QuestionAnswer
    GROUP BY [QuestionAnswer].Answer;

    Sample Results:


    • Edited by RunningManHD Thursday, November 19, 2015 2:37 PM
    Thursday, November 19, 2015 2:29 PM
  • My SQL is the basic sum query:

    SELECT Count(tblEdDocumentationMonitor.[Triage Start Date/Time]) AS CountofCases, tblEdDocumentationMonitor.[Triage Start Date/Time] AS Grouped
    FROM tblEdDocumentationMonitor
    GROUP BY tblEdDocumentationMonitor.[Triage Start Date/Time];

    Thursday, November 19, 2015 2:39 PM
  • My SQL is the basic sum query:

    SELECT Count(tblEdDocumentationMonitor.[Triage Start Date/Time]) AS CountofCases, tblEdDocumentationMonitor.[Triage Start Date/Time] AS Grouped
    FROM tblEdDocumentationMonitor
    GROUP BY tblEdDocumentationMonitor.[Triage Start Date/Time];

    Using the example provided above, your query would look like the following:

    SELECT tblEdDocumentationMonitor.[Triage Start Date/Time], Count(tblEdDocumentationMonitor.[Triage Start Date/Time]) AS Total, Count(tblEdDocumentationMonitor.[Triage Start Date/Time])/(SELECT Count(tblEdDocumentationMonitor.[Triage Start Date/Time]) AS Total
    FROM QuestionAnswer, tblEdDocumentationMonitor) AS Percentage
    FROM tblEdDocumentationMonitor
    GROUP BY tblEdDocumentationMonitor.[Triage Start Date/Time];

    Thursday, November 19, 2015 2:59 PM
  • SELECT tblEdDocumentationMonitor.[Triage Start Date/Time], Count(tblEdDocumentationMonitor.[Triage Start Date/Time]) AS Total, Count(tblEdDocumentationMonitor.[Triage Start Date/Time])/(SELECT Count(tblEdDocumentationMonitor.[Triage Start Date/Time]) AS Total
    FROM QuestionAnswer, tblEdDocumentationMonitor) AS Percentage
    FROM tblEdDocumentationMonitor
    GROUP BY tblEdDocumentationMonitor.[Triage Start Date/Time];

    A small addendum.

    To get the Percentage you can multiply the Fraction (what is in the above query) by 100.

    Imb.

    Thursday, November 19, 2015 3:06 PM
  • The SQL looks like it is working perfectly.  I was getting crazy numbers 

    <tfoot></tfoot>
    qryEDMTriageSimpleCount
    Triage Start Date/Time Total Percentage
    N 3 6.46551724137931E-03
    Y 461 0.993534482758621

    So I divided the fraction by 100 and am now getting

    <tfoot></tfoot>
    qryEDMTriageSimpleCount
    Triage Start Date/Time Total Percentage
    N 3 0.646551724137931
    Y 461

    99.3534482758621


    Was that the correct way to go?
    Thursday, November 19, 2015 3:15 PM
  • I actually think I got what I wanted - I removed the divide by 100 - instead, I formated the field to % and it is doing exactly what I want.  I just need to write that SQL for 25 different questions! 

    Thank you for the help.

    TJ

    Thursday, November 19, 2015 3:17 PM
  • To see the Percentage field as percent vs. decimal fraction, you can open the query in design view and open the Properties dialog.  With the Percentage field selected, set the Format property to Percent.  You do not need to do any additional multiplication or division from what I originally provided to you.
    • Marked as answer by seebert Thursday, November 19, 2015 3:59 PM
    Thursday, November 19, 2015 3:23 PM
  • I just need to write that SQL for 25 different questions! 

    Hi TJ,

    Construct your SQL-string based on the variables in your 25 questions, and use that SQL-string for what you want, in a QueryDef or RecordSource.

    Imb.

    Thursday, November 19, 2015 4:04 PM