Aggregating data into groups

[Tom]

I want to summarize aggregated data into fixed groups. For example,
assume the following raw data:

Store Sales
A 100,000
B 150,000
C 75,000
D 50,000
E 5,000

I'd like to summarize the results as follows:

Sales Level Store Count
0 (to 49,000) 1
50,000 (to 99,000) 2
100,000 (and over) 2

 

[Michel Walsh]

Hi,


If the ranges are constant, you can use PARTITION (see help file),
otherwise, I would use a table to define them:

Ranges
RangeDesc FromThis ToThis
"0 to 49000" 0 50000
"50000-99000" 50000 100000
"100000+" 100000 1E10




the SQL statement:

SELECT RangeDesc, COUNT(*)
FROM myTable As a INNER JOIN Ranges As r
ON a.Sales >=r.FromThis AND a.Sales<r.ToThis
GROUP BY RangeDesc



Hoping it may help,
Vanderghast, Access MVP