Dealing with NA response

[rbb101]

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.

 

[John Spencer]

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