Dealing with NA response

Discussion in 'Microsoft Access Reports' started by rbb101, Apr 23, 2010.

  1. rbb101

    rbb101 Guest

    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.
     
    rbb101, Apr 23, 2010
    #1
    1. Advertisements

  2. rbb101

    John Spencer Guest

    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.
    >
     
    John Spencer, Apr 23, 2010
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Deuce Sapp

    Slow Response While Designing a Form with Linked Tables

    Deuce Sapp, Jul 9, 2003, in forum: Microsoft Access Reports
    Replies:
    1
    Views:
    547
    Deuce Sapp
    Jul 9, 2003
  2. Robert Sykes

    Dealing with Null Value's

    Robert Sykes, Apr 11, 2005, in forum: Microsoft Access Reports
    Replies:
    1
    Views:
    150
    Marshall Barton
    Apr 12, 2005
  3. Guest

    Dealing with missing headers on mult-pages pages

    Guest, Jul 6, 2006, in forum: Microsoft Access Reports
    Replies:
    2
    Views:
    165
    Guest
    Jul 6, 2006
  4. JMS

    Dealing with Pecentage Calculations in Reports

    JMS, May 9, 2007, in forum: Microsoft Access Reports
    Replies:
    0
    Views:
    201
  5. Sophie

    dealing with error message

    Sophie, Jan 20, 2008, in forum: Microsoft Access Reports
    Replies:
    1
    Views:
    129
    NetworkTrade
    Jan 23, 2008
Loading...

Share This Page