Grouping Records into buckets

[Guest]

I have a long list of records that I would like to group into buckets
(cohorts) according to a value. For example, it might look like this

Item Cost Profit %
Shoes 15 10%
Socks 10 12%
Belts 20 8%
Shirts 25 15%
Hats 22 12%

I'd like to build a query that groups them into "15 or less", "16-20", and
"Greater than 20" so that I can do calculations on the other fields (ie avg
profit). I know I can do it with nested if statements, but I have 15
buckets, and I'd like to be able to change the parameters easier than I could
with if statements. (Oh, and I'm no good at SQL or VBA, so an Access query
solution would be easiest.)

Thanks,
Zack

 

[John Vinson]

I have a long list of records that I would like to group into buckets
(cohorts) according to a value. For example, it might look like this

Item Cost Profit %
Shoes 15 10%
Socks 10 12%
Belts 20 8%
Shirts 25 15%
Hats 22 12%

I'd like to build a query that groups them into "15 or less", "16-20", and
"Greater than 20" so that I can do calculations on the other fields (ie avg
profit). I know I can do it with nested if statements, but I have 15
buckets, and I'd like to be able to change the parameters easier than I could
with if statements. (Oh, and I'm no good at SQL or VBA, so an Access query
solution would be easiest.)

Well, the language of Access queries IS SQL. The query grid isn't "the
query" - it's just a tool which helps you build SQL. For this
particular query you'll need to go beyond its capabilities though.

I'd suggest a table Buckets with fields Low, High, and Bucket:

0 15 "15 or lower"
15 20 "> 15 and <= 20"
<etc>

You'll then need a "Non Equi Join" query. Assuming that it's the Cost
field that you want put into buckets, first create a query joining
this table to the Buckets table, joining Cost to Low. As written it
will only match if the cost is exactly equal to the Low value. To fix
this, open the query in SQL view and edit

ON yourtable.Cost = Buckets.Low

to

ON yourtable.Cost > Buckets.Low AND yourtable.Cost <= Buckets.High

This query will now have a field named Bucket upon which you can
group, etc.

John W. Vinson[MVP]