Average percent of Yes responses by section in report

[GS500]

Just moved this from the Queries forum since it has more to do with Reports, sorry!

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
WHERE (((tblQuestions.CategoryIDFK)=[Forms]![frmReportSelector]![cboCategory]))
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?

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.

Thanks!

 

[JHB]

Put in a Grouphead and Groupfoot for Phases in your report, place a field in the Groupfoot and set the ControlSource for the field to "=Avg([YourFieldNameForYesResposes]).