Count records within a range of values

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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 said:
Please help me. I have a table with fields Salesman, Customer,
Click to expand...
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?
 
Back
Top