# Query Calculation problem

• ### 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

• 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.
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.