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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|