Counts within a given range or criteria

[Guest]

I'm trying to query two columns in a table. The first column is a customer
#, and the second column is various numerical values (ranging from 0 to 400)
for each customer.

I'm trying to build a query that will give me a COUNT of how many values
exist for each customer within a given range.

For instance, how many values fall between 0 and 20 for customer #1?
Customer #2? How many values fall between 20 and 40 for Customer #1?

I'm trying to get counts on these ranges in as few queries as possible
(there are only 3 customers), but I can't seem to get Access to give me
counts within specific ranges.

Any help would be greatly appreciated! Thank you.

 

[Dirk Goldgar]

I'm trying to query two columns in a table. The first column is a
customer #, and the second column is various numerical values
(ranging from 0 to 400) for each customer.

I'm trying to build a query that will give me a COUNT of how many
values exist for each customer within a given range.

For instance, how many values fall between 0 and 20 for customer #1?
Customer #2? How many values fall between 20 and 40 for Customer #1?

I'm trying to get counts on these ranges in as few queries as possible
(there are only 3 customers), but I can't seem to get Access to give
me counts within specific ranges.

Any help would be greatly appreciated! Thank you.

Here's an example, but it has the drawback that if there are no values
in a particular range for a customer, there will be no record for that
range in the results:

SELECT
CustomerNo,
Partition([TheValue],0,400,20) AS Range,
Count(TheValue) AS NumInRange
FROM CustomerValues
GROUP BY CustomerNo, Partition([TheValue],0,400,20) ;

In the above substitute your table and field names for "CustomerValues",
"CustomerNo", and "TheValue".

If you need to have every range represented, whether there's any value
in it or not, you're going to have to create a separate table of the
ranges you want, with a name, a start-value, and a stop-value for each
range, and bring that table into your query, joining it to the original
table on the expression

[TheValue] Between [RangeStart] And [RangeEnd]

and grouping on CustomerNo and RangeName.

 

[Guest]

Thanks Dirk. That worked GREAT! The only thing I had to change was to add in
a set of brackets in one spot, changing your line:

Count(TheValue) AS NumInRange

to:

Count([TheValue]) AS NumInRange

You are "the man."