Counts from multiple ranges within one query

[Guest]

Hi. I'm doing counts of how many sold products fall within a given range of
product numbers. For instance the query SQL below gives me a count of how
many products had product numbers between 12500 and 13717.

My problem is that I'm trying to do these counts for several different
ranges, and can't figure out how to do it all in one query. I should mention
that the ranges over which I'm counting aren't always the same either (ie,
they're not at regular intervals).

So right now I'm doing a separate query for every single range! Is there
some way I can do this all in one query?

Thanks in advance!

SELECT DISTINCTROW Count(ProductSales.ProductNumber) AS RangeOne
FROM ProductSales
WHERE (((ProductSales.ProductNumber)>12500 And
(ProductSales.ProductNumber)<13717));

 

[Allen Browne]

The simplest approach might be to create a table to hold the ranges.

You could then add the range table to the query (with no join), and get the
count of each range with something like this:

SELECT Count(ProductSales.ProductNumber) AS RangeOne
FROM ProductSales, tblRange
WHERE ProductSales.ProductNumber Between tblRange.Start And tblRange.End;

The basic idea is that 2 tables in a query without a join yield every
possible combination of the two. You then limit the results with the WHERE
clause to get the count of items within the range. That should work with
multiple ranges, even if your ranges have gaps or overlap.

 

[David Seeto via AccessMonster.com]

You could create another table to hold your beginning and end ranges, like
so:
Table: ProductSelection
Column: StartProduct
Column: EndProduct

Your query then becomes:
SELECT Count(ProductSales.ProductNumber) AS RangeOne
FROM ProductSales, ProductSelection
WHERE (((ProductSales.ProductNumber)>=[StartProduct] And
(ProductSales.ProductNumber)<=[EndProduct]));

That just gives you the total count for all products that match the range.
If you want to see how many exist in each range, use this query:
SELECT ProductSelection.StartProduct, ProductSelection.EndProduct, Count
(ProductSales.ProductNumber) AS RangeSeveral
FROM ProductSales, ProductSelection
WHERE (((ProductSales.ProductNumber)>=[StartProduct] And
(ProductSales.ProductNumber)<=[EndProduct]))
GROUP BY ProductSelection.StartProduct, ProductSelection.EndProduct;