A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Dealing with NA response



 
 
Thread Tools Display Modes
  #1  
Old April 23rd, 2010, 05:19 PM posted to microsoft.public.access.reports
rbb101
external usenet poster
 
Posts: 18
Default Dealing with NA response

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.

  #2  
Old April 23rd, 2010, 05:34 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default Dealing with NA response

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
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:00 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.