Compliance Report

[cocoaice]

I have a table with a date field and five different questions that can only be answered as "Yes", "No", "N/A".

I am trying to run a report that calculates compliance for each question at a determined date range parameter.

TblData([dtmReportDate],[strQ1],[strQ2],[strQ3],[strQ4],[strQ5])

I would like to run a report that When the user enters a date range, the report returns results like

Question 1, Question 2, Question 3, Question 4, Question 5
10/20=50%, 39/50=78%, 15/50=30%, 10/30=33%, 20/40=50%

The numerator is the count of the questions that were answered "yes"
The denominator is the count of the questions that were answered only "yes" or "no"
The Compliance is the percentage of the questions answered yes by the total questions answered yes or no

 

[Remou]

Here are four queries.

SELECT t.dtmReportDate, "q1" as QNo, Switch(t.strQ1="Yes",1,t.strQ1="No",0) As Ans
FROM TblData t
UNION ALL
SELECT t.dtmReportDate, "q2" as QNo,Switch(t.strQ2="Yes",1,t.strQ2="No",0) As Ans
FROM TblData t
UNION ALL
SELECT t.dtmReportDate, "q3" as QNo, Switch(t.strQ3="Yes",1,t.strQ3="No",0) As Ans
FROM TblData t
UNION ALL
SELECT t.dtmReportDate, "q4" as QNo,Switch(t.strQ4="Yes",1,t.strQ4="No",0) As Ans
FROM TblData t
UNION ALL SELECT t.dtmReportDate, "q5" as QNo,Switch(t.strQ5="Yes",1,t.strQ5="No",0) As Ans
FROM TblData t

PARAMETERS SelDate DateTime;
TRANSFORM Sum(qryUnionData.Ans) AS Expr1
SELECT qryUnionData.dtmReportDate
FROM qryUnionData
WHERE (((qryUnionData.dtmReportDate)=[SelDate]))
GROUP BY qryUnionData.dtmReportDate
PIVOT qryUnionData.QNo;

PARAMETERS SelDate DateTime;
TRANSFORM Count(qryUnionData.Ans) AS Expr1
SELECT qryUnionData.dtmReportDate
FROM qryUnionData
WHERE (((qryUnionData.dtmReportDate)=[SelDate]))
GROUP BY qryUnionData.dtmReportDate
PIVOT qryUnionData.QNo

SELECT c.dtmReportDate, ([s].[q1]/[c].[q1])*100 AS Q1, ([s].[q2]/[c].[q2])*100 AS Q21, ([s].[q3]/[c].[q3])*100 AS Q3, ([s].[q4]/[c].[q4])*100 AS Q4, ([s].[q5]/[c].[q5])*100 AS Q5
FROM qryDataCount AS c INNER JOIN qryDataSum AS s ON c.dtmReportDate = s.dtmReportDate

 

[Golom]

A bit klunky but try this

Select
  (Select Count(*) From tbl Where strQ1 = 'Yes') As Q1Yes,
  (Select Count(*) From tbl Where strQ1 IN ('Yes','No')) As Q1YesNo,

 ((Select 100 * Count(*) From tbl Where strQ1 = 'Yes') / 
  (Select Count(*) From tbl Where strQ1 IN ('Yes','No'))) As Q1Percent,

[blue] ... etc. for Q2 to Q5 ...[/blue]

WHERE [dtmReportDate] BETWEEN [StartDate] AND [EndDate]

 

[lespaul]

First you need to create a query that normalizes your data:

SELECT dtmREportDate, "Q1" As Question, strQ1 As Answer FROM TblData
UNION
SELECT dtmREportDate, "Q2", strQ2 FROM TblData
UNION
SELECT dtmREportDate, "Q3", strQ3 FROM TblData
UNION
SELECT dtmREportDate, "Q4", strQ4 FROM TblData
UNION
SELECT dtmREportDate, "Q5", strQ5 FROM TblData

Now you can use this query as the source for other queries to get the information you need:

SELECT dtmReportDate, Question, Answer, count(*) as totals FROM qryNormal GROUP BY dtmReportDate, Question, Answer

results:
[tt]
dtmReportDate Question Answer Totals
1/1/2008 Q1 Yes 10
1/1/2008 Q1 No 5
1/1/2008 Q1 N/A 2
[/tt]

HTH




Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[cocoaice]

I went over Golom's idea.

This is my current SQL

Select
  
(Select Count(*) From qry_Data Where HOB30 = 'Yes' AND Date BETWEEN [StartDate] AND [EndDate] ) As Q1Yes,
  
(Select Count(*) From qry_Data Where HOB30 IN ('Yes','No')AND Date BETWEEN [StartDate] AND [EndDate]) As Q1YesNo,

Format ((Select Count(*) From qry_Data Where HOB30 = 'Yes' AND Date BETWEEN [StartDate] AND [EndDate]) / (Select Count(*) From qry_Data Where HOB30 IN ('Yes','No') AND Date BETWEEN [StartDate] AND [EndDate]),"0%") As Q1Percent,

..... Repeat for next questions....

FROM qry_Data

My problem is that this query is returning multiple lines. (one for each record on this table)

Q1Yes	Q1YesNo	Q1Percent
20	20	100%
20	20	100%
20	20	100%
20	20	100%
20	20	100%
20	20	100%
20	20	100%
20	20	100%
20	20	100%
20	20	100%
20	20	100%
20	20	100%
20	20	100%
20	20	100%
20	20	100%
20	20	100%
20	20	100%
20	20	100%
20	20	100%
20	20	100%
20	20	100%

How do I make this query to respond only with one row?

 

[cocoaice]

I figured out myself the next step

I Used

 SELECT TOP 1 ...........

Thanks everyone for sharing your knowledge. I certainly learned a lot about UNION queries, which I had no idea what they were for.