Count records within a range of values

Please help me. I have a table with fields Salesman, Customer, Comparison(% of sales volume compared to last quarter). I would like a query that gives the count by salesman of the number of customers with sales in 4 ranges. =50%, between 51 and 100, between 101 and 150 and 150%. How can I do this with one query?

Hi,


TRANSFORM COUNT(*)
SELECT salesman
FROM myTable
GROUP BY salesman
PIVOT SWITCH( comparison=.5, "50%",
comparison=1, "51-100%",
comparison=1.5, "101-150%",
TRUE, "150%" )



or similar, should do. A more versatile solution is to enter the constants
in a table, to make an inner join with that table, and change the PIVOT
clause accordingly.


TRANSFORM COUNT(*)
SELECT salesman
FROM myTable INNER JOIN scale
ON myTable.Comparison BETWEEN scale.Low AND scale.High
GROUP BY salesman
PIVOT scale.Description




Hoping it may help,
Vanderghast, Access MVP





"Steve" wrote in message
news
Please help me. I have a table with fields Salesman, Customer,
Comparison(% of sales volume compared to last quarter). I would like a
query that gives the count by salesman of the number of customers with sales
in 4 ranges. =50%, between 51 and 100, between 101 and 150 and 150%. How
can I do this with one query?