Thread: Query counting

1. Registered User
Join Date
Nov 2003
Posts
4

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. Cavalier King Charles
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.

3. Registered User
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
•