Grouping items into buckets/cohorts

[Guest]

I've got a group of data that I'm trying to group into buckets according to
some characteristics and then do calculations on others. For example:

Item Price Profit%
Shoes 10 8%
Socks 15 15%
Belts 18 7%
Shirts 20 18%
Hats 22 6%

I'd like to group them into buckets like price "<= 15","15-20",and "> 20"
and then do calculations on avg profit. I know I can do this with nested if
statements, but I have about 15 buckets to group in and I want to be able to
change my parameters easier than if/then statements would allow. I am no
expert in SQL or VBA, so any solution using standard Access tools would be
great.

Thanks so much for the help.
Z

 

[George Nicholson]

If you have as many as 15 buckets, I would either add a "GroupType" field to
an existing ProductList table or create a new table to hold that info, which
can then be easily joined to your "real" data in a query:

Product GroupType
Shoes <15
Socks 15-20
Belts 15-20
etc...

Then it would be a simple matter to create a cross-tab query (using
GroupType as a Row or Column Heading), or create a Pivot table on your data.
Probably easier to maintain & change than a convoluted nested "if"
statement.

--
HTH,

George Nicholson


(Please post responses to newsgroup but remove "Junk" from return address if
used)