I am wondering if there is a better approach to designing a report.

I have a data based used to evaluate about 30 attributes. Each attribute
has a combo box which allows the user to select either “Yes”, “No” or “NA”.

The purpose of the report is to provide a numerical average based upon text
responses. For example, if there were 7 Yes responses out of possible 10,
the result would be 7 divided by 10 or 70%.

Since one of the possibilities is an NA response, I need to take the number
of NA responses out of the equation. Therefore, if there were 7 Yes, and 2
NA the result would be 7 divided by 8 or 87.5%

The report is based upon a query that has several expressions for each
attribute.

ExprCov1a: IIf([Cov1]="Yes",1,0) Assigns a numerical value to each
responses

ExprCov1: IIf([Cov1]="Yes",1,IIf([Cov1]="No",0,-1)) Used to ID NA
responses (-1)

The control source of the text box in the report is:

=(Sum([qryQAReport]![ExprCov1a]))/(Sum(Abs([qryQAReport]![ExprCov1])))

Is there an easier way to get the average.

SELECT Sum(Cov1="Yes")/Sum(Cov1<>"NA") as Cov1Percentage
, Sum(SomeOtherAttribute="Yes")/Sum(SomeOtherAttribute<>"NA") as
SomeOtherAttributePercentage
FROM SomeTable

Or if you have to do this in the report
=Sum(Cov1="Yes")/Sum(Cov1<>"NA")


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

rbb101 wrote:
> I am wondering if there is a better approach to designing a report.
>
> I have a data based used to evaluate about 30 attributes. Each attribute
> has a combo box which allows the user to select either “Yes”, “No” or “NA”.
>
> The purpose of the report is to provide a numerical average based upon text
> responses. For example, if there were 7 Yes responses out of possible 10,
> the result would be 7 divided by 10 or 70%.
>
> Since one of the possibilities is an NA response, I need to take the number
> of NA responses out of the equation. Therefore, if there were 7 Yes, and 2
> NA the result would be 7 divided by 8 or 87.5%
>
> The report is based upon a query that has several expressions for each
> attribute.
>
> ExprCov1a: IIf([Cov1]="Yes",1,0) Assigns a numerical value to each
> responses
>
> ExprCov1: IIf([Cov1]="Yes",1,IIf([Cov1]="No",0,-1)) Used to ID NA
> responses (-1)
>
> The control source of the text box in the report is:
>
> =(Sum([qryQAReport]![ExprCov1a]))/(Sum(Abs([qryQAReport]![ExprCov1])))
>
> Is there an easier way to get the average.
>