none
Query Calculation problem RRS feed

  • Question

  • I am trying to get a query to produce the percentage of a yes/no answer to a question. Right now the results look like this:

    <tfoot></tfoot>
    qryEDMTriageSimpleCount
    Triage Start Date/Time Total Percentage

    0 0.00%
    N 2 0.22%
    Y 201 22.36%

    So the percentage definitely isn't calculating right as the yes should be more like 98%.  Here is the SQL that feeds the calculation - any idea where I am going wrong with this

    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  tblEdDocumentationMonitor) AS Percentage
    FROM tblEdDocumentationMonitor
    WHERE (((tblEdDocumentationMonitor.Date) Between [Enter Start Date] And [Enter End Date]))
    GROUP BY tblEdDocumentationMonitor.[Triage Start Date/Time];

    Appreciate any help I can get to solve this one.

    Thanks!

    Monday, April 4, 2016 12:12 PM

Answers

  • I am trying to get a query to produce the percentage of a yes/no answer to a question.

    Is this the sort of thing you have in mind?

    PARAMETERS  [Enter Start Date] DATETIME,
    [Enter End Date] DATETIME;
    SELECT COUNT(*) AS Total,
    SUM(IIF([Triage Start Date/Time]= "Y",1,0))/COUNT(*)*100 AS PercentageYes,
    SUM(IIF([Triage Start Date/Time]= "N",1,0))/COUNT(*)*100 AS PercentageNo,
    SUM(IIF([Triage Start Date/Time] IS NULL,1,0))/COUNT(*)*100 AS PercentageUnspecified
    FROM  tblEdDocumentationMonitor
    WHERE [Date] BETWEEN [Enter Start Date] AND [Enter End Date];

    In view of the Nulls I've assumed that Triage Start Date/Time is a column of text data type rather than Boolean data type.

    I'd strongly advise that you do not use Date as a column name, however.  As the name of a built in function it's a 'reserved word' and should not be used as an object name.  Use something more specifically descriptive of the attribute.


    Ken Sheridan, Stafford, England

    Tuesday, April 12, 2016 8:41 PM

All replies

  • I think that you don't show the whole query. It looks like [Triage Start Date/Time] contains the values Y, N and Null (or blank). This does not really make sense. However, guessing that this is another grouping variable, say, "Result", and that you want the percentage calculated by [Triage Start Date/Time], you could try

    SELECT EDM.[Triage Start Date/Time],
           EDM.Result,
           Count(EDM.[Triage Start Date/Time]) AS Total, 
           (SELECT Count(Temp.[Triage Start Date/Time]) 
              FROM tblEdDocumentationMonitor AS Temp
             WHERE Temp.[Triage Start Date/Time] = EDM. [Triage Start Date/Time]) As Base,
             Total / Base As Percentage 
     FROM tblEdDocumentationMonitor As EDM
     WHERE (((EDM.Date) Between [Enter Start Date] And [Enter End Date]))
     GROUP BY EDM.[Triage Start Date/Time], EDM.Result;

    This is untested air code.

    Matthias Kläy, Kläy Computing AG

    Monday, April 4, 2016 1:10 PM
  • I tried to make your suggestion work and that it isn't.  Here is the design of the query:

    I tend to miss the most obvious things.  Maybe seeing the whole query will help.

    Monday, April 4, 2016 1:25 PM
  • Monday, April 4, 2016 1:27 PM
  • Again: In the original post, you show the output of the query. It has 3 columns, "Triage Start Date/Time", "Total" and "Percentage". Under the Heading "Triage Start Date/Time" I see the values (blank), N and Y. Is this correct?

    Matthias Kläy, Kläy Computing AG

    Monday, April 4, 2016 2:30 PM
  • Yes - the problem is that it totals the records based on the Y, N or blanks.  The percentage column is not working.  I need the percentage of cases that were yes, no or blanks and that definitely is not working correctly.
    Monday, April 4, 2016 3:05 PM
  • I still believe that there is a column missing from your query.

    It just does not make sense that in the Where-Clause you ask for Start and End Dates for the column [Triage Start Date/Time]:

    WHERE (((tblEdDocumentationMonitor.Date) Between [Enter Start Date] And [Enter End Date]))

    and that this same column contains only values blank, N, and Y?

    Could you post the structure (all fields with their data types and the primary key) of table tblEdDocumentationMonitor ?

    Matthias Kläy, Kläy Computing AG

    Monday, April 4, 2016 3:15 PM
  • To me it looks like you are (deliberately?) complicating things. When things are not working as you'd expect then simplify. And simplify again if need be.
    201 people has answered Yes, 2 people has answered No:
    SELECT yesPeople, noPeople FROM theTable

    Debug.Print (noPeople / yespeople) * 100

    ... go on from there ....


    Best regards, George



    Tuesday, April 12, 2016 4:05 PM
  • I am trying to get a query to produce the percentage of a yes/no answer to a question.

    Is this the sort of thing you have in mind?

    PARAMETERS  [Enter Start Date] DATETIME,
    [Enter End Date] DATETIME;
    SELECT COUNT(*) AS Total,
    SUM(IIF([Triage Start Date/Time]= "Y",1,0))/COUNT(*)*100 AS PercentageYes,
    SUM(IIF([Triage Start Date/Time]= "N",1,0))/COUNT(*)*100 AS PercentageNo,
    SUM(IIF([Triage Start Date/Time] IS NULL,1,0))/COUNT(*)*100 AS PercentageUnspecified
    FROM  tblEdDocumentationMonitor
    WHERE [Date] BETWEEN [Enter Start Date] AND [Enter End Date];

    In view of the Nulls I've assumed that Triage Start Date/Time is a column of text data type rather than Boolean data type.

    I'd strongly advise that you do not use Date as a column name, however.  As the name of a built in function it's a 'reserved word' and should not be used as an object name.  Use something more specifically descriptive of the attribute.


    Ken Sheridan, Stafford, England

    Tuesday, April 12, 2016 8:41 PM
  • Hi, seebert

    Try it I make a change in that.

    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  tblEdDocumentationMonitor) AS Percentage
     FROM tblEdDocumentationMonitor
     WHERE (((tblEdDocumentationMonitor.Date) Between [Enter Start Date] And [Enter End Date]))
     GROUP BY tblEdDocumentationMonitor.[Triage Start Date/Time];

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, April 13, 2016 4:43 AM
    Moderator