Count records within a range of values

[Guest]

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?

 

[Michel Walsh]

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

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?