Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jul 2002
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count function (Access 97)

    I'm running reports on a survey that was entered in a data entry form. The questions with a "Yes", "No" were entered via Option Box where 1=Yes, 2=No.

    Now what I want to do is out of the total number of records entered, what is the percentage who answered "Yes". Is there a way I can do the queries easily. I've been creating one query that holds only the total number of records entered, then there is another query where in one field pulls out all the one's (Yes), another field which holds the total number of records, then in another field an expression that looks like "2 Yes: Count(*)/[Total Respondents]".

    However, I have to do this for each "Yes", "No for each question. Is there a way I can do an IIf statement, or at the very least, reduce the number of queries I need to get the results I'm looking for? I've tried "Yes: IIf([Question1]![Answer]=1,Count(*)/[Total Number of Respondents],"")

    I hope I've explained this properly. I'm not very familiar with VB, can anyone help me.

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count function (Access 97)

    This is causing you difficulty because I believe the normalized way to design this is one table for the Survey questions, one table for the Respondents and one table for each respondent's Answer to a question. But given that you may not want to redesign this ...

    I hesitate to recommend domain aggregate functions because they can really slow things down, but on an unbound report a textbox with =DCount("[yourquestion]","yourtable","[yourquestion]='1' ") will give you the total number of respondents that answered yes to [yourquestion].
    A textbox with =Format((DCount("[yourquestion]","yourtable","[yourquestion]='1' ")/DCount("[yourquestion]","yourtable","[yourquestion]<>'0'")*100),"0") & "%" will give you the percentage of the respondents that answered yes to [yourquestion].
    HTH

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count function (Access 97)

    The simple way is to Sum each yes/no column, since the Yes/no values equate to -1 or zero. If you do a totals query, use the Sum aggregate instead of the GroupBy for each yes/no column and that should give you the count, although you'll have to either use the Abs() function or multiply the total by -1 to get a positive number.
    Charlotte

  4. #4
    Lounger
    Join Date
    Jul 2002
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count function (Access 97)

    Thank you that worked beautifully.

    The only problem I now have is that it give me a result for each record in the database. For example, if I have 10 records, the percentage shows up 10 times.

    How do I fix this?

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count function (Access 97)

    I don't understand how this could be happening if your report is unbound, which means it has no record source.
    Look in the report properties and make sure the Record Source property is blank.
    To identify the domain aggregate statistics, you will need to place labels on the report identifying the calculation results.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •