Thread Tools Rate Thread

# Dealing with NA response

rbb101
Guest
Posts: n/a

 23rd Apr 2010
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
Guest
Posts: n/a

 23rd Apr 2010
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.
>

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Frank Microsoft Outlook Discussion 3 17th Jun 2005 09:26 PM Lookyt Microsoft Outlook 4 7th Apr 2004 03:28 AM Promenade Microsoft ASP .NET 2 5th Dec 2003 07:41 PM M. Abell Microsoft Outlook 1 2nd Dec 2003 04:51 PM Matthew Wieder Microsoft ASP .NET 4 17th Jul 2003 06:33 PM

Features

Advertising

Newsgroups

All times are GMT +1. The time now is 12:25 PM.