Unanswered: Calculate average of Yes answers by section in report
Iím working on a file audit database, which has a series of questions with possible Yes, No, or NA responses.
Iím using the At Your Survey database as a guide, but there are a few details in my database that are different from the AYS database and I canít seem to figure out how to implement the details.
I have built a Report that gives me the % of Yes, No, and NA responses to each question. That part works fine.
But my questions are broken down into various Phases with a few questions per Phase. I need to take the percentages of Yes responses for each question in a Phase and average them together to come up with a Phase Average. So for instance:
Question1 75% Question2 50% Phase 1 Avg 63%
My report is based on the following crosstab query:
PARAMETERS [Forms]![frmReportSelector]![cboCategory] Long;
TRANSFORM Count(tblResults.Answer) AS CountOfAnswer
SELECT tblQuestions.QstnID, tblQuestions.QstnNum, tblQuestions.SubQstnNum, tblQuestions.QstnText, tblResults.Answer, tblQuestions.CategoryIDFK, tblQuestions.PhasesIDFK
FROM tblCatResult INNER JOIN (tblQuestions INNER JOIN tblResults ON tblQuestions.QstnID = tblResults.QstnID) ON tblCatResult.ResultID = tblResults.ResultID
GROUP BY tblQuestions.QstnID, tblQuestions.QstnNum, tblQuestions.SubQstnNum, tblQuestions.QstnText, tblResults.Answer, tblQuestions.CategoryIDFK, tblQuestions.PhasesIDFK
PIVOT "Num of Results";
The ďNum of ResultsĒ field counts the number of Yes, No and NAís for each question. I have a textbox [txtTotResult] that Sums the Y,N,NA responses for a total number of responses to each question, and a textbox that divides [Num of Results]/[txtTotResult] to give the percent for each question.
But Iím stumped on how to do the Phase average. Can anyone shed some light on this? My report is grouped by phase and I assume I need a text box with an expression for the average in the Phase footer, but I can't figure out what the expression should be.
Iím sure Iíve left out some crucial detail that you guys will need to come up with an answer, so please let me know what other info you need.