PC Review


Reply
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.

 
Reply With Quote
 
 
 
 
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.
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Undeliverable response to meeting response Frank Microsoft Outlook Discussion 3 17th Jun 2005 09:26 PM
Response Library Add-In - Response Templates! Lookyt Microsoft Outlook 4 7th Apr 2004 03:28 AM
Response.Expires and Response.Cache Promenade Microsoft ASP .NET 2 5th Dec 2003 07:41 PM
Auto Response or Vacation Response M. Abell Microsoft Outlook 1 2nd Dec 2003 04:51 PM
Response.Write and Response.Redirect 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:45 AM.