To follow up on Jeff's post
Between xxxx And yyyy
is equivalent to
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
