Criteria based on ranges

  • Thread starter Thread starter RFJ
  • Start date Start date
R

RFJ

I have a table called 'ranges' - with two fields (named 'high' and 'low').
ie :

Low High 'fields name
0 10 'values
10 50
50 100
100 500
500 10000

I have another table called 'organisations' with a fieldname called 'size'.
(It contains the number of people employed in the organisation.) In the
table are details of about 50 organisations.

I want to create a query that will tell me how many organisations fall into
each range (eg 12 organisations with 0 to 10 staff, etc.).

Can SKS help.

TIA
 
First, you have non-exclusive ranges ... into which row would a value of 50
fall?

In your query, you can use the "Between xxxx And yyyy" expression in your
criterion for "size", provided your organizations table has the actual
number of employees.
 
To follow up on Jeff's post

Between xxxx And yyyy

is equivalent to
= xxxx And <= yyyy
Click to expand...

So, if you use a query based on Between...And whose SQL looks something like

SELECT
[ranges].[low],
[ranges].[high],
Count(*) AS [number of organizations]
FROM
[organizations],
[ranges]
WHERE
[organizations].[size] Between [ranges].[low] And [ranges].[high]
GROUP BY
[ranges].[low],
[ranges].[high]

an organization with a "size" of 50 (or any other size that falls on your
range boundaries) will "match" two records in "ranges", and will get counted
twice, which I think was what Jeff was suggesting.

If instead you use something like

SELECT
[ranges].[low],
[ranges].[high],
Count(*) AS [number of organizations]
FROM
[organizations],
[ranges]
WHERE
[organizations].[size] >= [ranges].[low]
AND
[organizations].[size] < [ranges].[high]
GROUP BY
[ranges].[low],
[ranges].[high]

an organization will not get counted twice (assuming that your ranges do not
overlap). For example, an organization with a "size" of 50 will fall into
the 50 to 500 range.

Since your ranges start from 0, and you are presumably dealing with a
discrete size, you might want to use "right-continuous" conditions instead,
like

[organizations].[size] < [ranges].[low]
AND
[organizations].[size] <= [ranges].[high]

in which case an organization with a "size" of 50 would fall into the 10 to
50 range. It all depends on how you want to handle the boundary conditions.
 
Thanks Brian,

Perfect solution <G>


Brian Camire said:
To follow up on Jeff's post

Between xxxx And yyyy

is equivalent to
= xxxx And <= yyyy
Click to expand...

So, if you use a query based on Between...And whose SQL looks something like

SELECT
[ranges].[low],
[ranges].[high],
Count(*) AS [number of organizations]
FROM
[organizations],
[ranges]
WHERE
[organizations].[size] Between [ranges].[low] And [ranges].[high]
GROUP BY
[ranges].[low],
[ranges].[high]

an organization with a "size" of 50 (or any other size that falls on your
range boundaries) will "match" two records in "ranges", and will get counted
twice, which I think was what Jeff was suggesting.

If instead you use something like

SELECT
[ranges].[low],
[ranges].[high],
Count(*) AS [number of organizations]
FROM
[organizations],
[ranges]
WHERE
[organizations].[size] >= [ranges].[low]
AND
[organizations].[size] < [ranges].[high]
GROUP BY
[ranges].[low],
[ranges].[high]

an organization will not get counted twice (assuming that your ranges do not
overlap). For example, an organization with a "size" of 50 will fall into
the 50 to 500 range.

Since your ranges start from 0, and you are presumably dealing with a
discrete size, you might want to use "right-continuous" conditions instead,
like

[organizations].[size] < [ranges].[low]
AND
[organizations].[size] <= [ranges].[high]

in which case an organization with a "size" of 50 would fall into the 10 to
50 range. It all depends on how you want to handle the boundary conditions.


RFJ said:
I have a table called 'ranges' - with two fields (named 'high' and 'low').
ie :

Low High 'fields name
0 10 'values
10 50
50 100
100 500
500 10000

I have another table called 'organisations' with a fieldname called 'size'.
(It contains the number of people employed in the organisation.) In the
table are details of about 50 organisations.

I want to create a query that will tell me how many organisations fall into
each range (eg 12 organisations with 0 to 10 staff, etc.).

Can SKS help.

TIA
Click to expand...
Click to expand...
 
Back
Top