Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    4

    Red face Unanswered: Counting Query---Puzzled

    Wondering if someone can a newbie with getting access to count a numeric field as if it were a yes/no check box.

    In this case, I'm looking at merit pay for a group of employees. My table simply lists the employee name, etc. and the amount of pay. I'm trying to design a query that will return a percentage of the workers who are actually receiving merit pay.

    So, a $0 dollar amount would be a "no" and anything greater than zero would be a "yes". Then, I'd want to calculate the percentages for a given group...for example....



    Percent of those getting Merit

    Men..........................................30%
    Women......................................41&

    I've tried books, used help, etc. but can't figure a way to make Access give me this number.

    Any help is much appreciated!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    percentages calculated by separate gender:
    Code:
    select 'Men'
         , count(*) * 100.0 / 
           ( select count(*) 
               from yourtable
              where gender='M' )
      from yourtable
     where gender='M'
       and meritpay > 0
    union all
    select 'Women'
         , count(*) * 100.0 / 
           ( select count(*) 
               from yourtable
              where gender='F' )
      from yourtable
     where gender='F'
       and meritpay > 0
    if you want gender percentages of entire file, remove the gender test from the subqueries


    rudy
    http://rudy.ca/

Posting Permissions

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