Results 1 to 3 of 3

Thread: Query counting

  1. #1
    Join Date
    Nov 2003
    Posts
    4

    Exclamation Unanswered: Query counting

    I have a number of different tables that represent different company divisions

    Division A has ID number, Salary, age, car.

    I need to create a query that counts people according to their pay band
    and then be able to apply this query to different divisions.

    I have created another table 'IncomeRange' that has 3 values, higher income, medium income, lower income. I want to use this table to query the different Company divions to give me a breakdown of how many people fall in which salary range.

    eg.

    Division A Table

    ID Salary Age Car
    001 13000 21
    002 55000 45 BMW
    003 23000 19 Honda
    004 16000 25
    005 18000 24 Mini
    006 16000 18

    IncomeRange Table
    higher income medium income lower income
    20000 15000 1000

    The query should give me

    number of ppl earning more then 20000, number of people greater then medium but less then higher. number of people earning low pay but less then medium.


    High Medium Low
    2 3 1

    So by adjusting the vaules in the Income Rnage table I can get different results

    Help!!

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you probably can't get where you want to go with that approach.

    if you are using saved queries in access, consider trying this:

    make a new "global" module
    add some functions:


    public function salValTop() as long
    salValTop = 20000 'see * below
    end function

    * hard coding the values into the function is ugly. better is some constants at the top of this module. best is a form with three boxes for you to fill in the values at runtime: the function becomes
    salValTop = Forms!nameOfTheForm.nameOfTheBox

    similarly salValMid & salValLow

    now you can use these functions in your saved queries

    add three calc fields to the query along the lines of:

    Expr1: iif(theSalary > salValTop(), 1, 0)
    Expr2: iif((theSalary < salValTop()) and (theSalary > salValMid()), 1,0)
    Expr3: iif((theSalary < salValMid() and (theSalary > salValLow()), 1,0)

    the sum (not count!!) of Expr1, 2, 3 should be what you are looking for.

    izy

    EDIT: missing ) in 3
    Expr3: iif((theSalary < salValMid()) and (theSalary > salValLow()), 1,0)
    Last edited by izyrider; 12-04-03 at 09:51.
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Re: Query counting

    Originally posted by uk1
    I have a number of different tables that represent different company divisions

    Division A has ID number, Salary, age, car.

    I need to create a query that counts people according to their pay band
    and then be able to apply this query to different divisions.

    I have created another table 'IncomeRange' that has 3 values, higher income, medium income, lower income. I want to use this table to query the different Company divions to give me a breakdown of how many people fall in which salary range.

    eg.

    Division A Table

    ID Salary Age Car
    001 13000 21
    002 55000 45 BMW
    003 23000 19 Honda
    004 16000 25
    005 18000 24 Mini
    006 16000 18

    IncomeRange Table
    higher income medium income lower income
    20000 15000 1000

    The query should give me

    number of ppl earning more then 20000, number of people greater then medium but less then higher. number of people earning low pay but less then medium.


    High Medium Low
    2 3 1

    So by adjusting the vaules in the Income Rnage table I can get different results

    Help!!
    To calculate the number of people per salary range you can use a query such as this:

    SELECT COUNT(*) AS [NUM OF PEOPLE], INCOME_RANGE
    FROM DIVISION_TABLE
    INNER JOIN
    INCOME_RANGE_TABLE
    ON
    SALARY >= MIN_INCOME_RANGE
    AND
    SALARY <= MAX_INCOME_RANGE
    GROUP BY
    INCOME_RANGE

    your income table would have to be changed to the following structure:
    INCOME_RANGE_ID (PRIMARY KEY NUMBER)
    INCOME_RANGE (TEXT EG
    HIGH INCOME, MEDIUM INCOME ETC)
    MIN_INCOME_RANGE (NUMBER)
    MAX_INCOME_RANGE (NUMBER)

    You could go one stage further and use a union query to join the division tables together, provided they have the same structure and then apply the above query over the union query

Posting Permissions

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