Query to generate columns determined by the count of a field within different ranges

[JSvar]

I am trying to create a query that uses the count of a specific field and generates columns for different range values. For Example.

Count(terminal_city_locations)

City Count >1 and <6 Count >5 and <11 Count >10 and <16

London 3
Munich 6
Paris 13

I can get the count for each city, but am struggling with coming up with a way to format the results into a table that reflects the information. When I try to use multiple Counts with different ranges for the value I get no results since no city satisfies more than one of the conditions.



Is something like this possible?

[June7]

This could require several queries.

Need a field that will assign a group identifier.

Build a query with the count query:

SELECT terminal_city_locations, IIf(CountCity<6, ">1 and <5", IIf(CountCity<11, ">5 and <11", IIf(CountCity<16, ">10 and <16", ">16"))) AS CountGroup FROM CountQuery;

Then take that query and build a CROSSTAB with it.

[Dal Jeanis]

This can be done easily using a crosstab query, but you probably just need a simple query with a "spreader" feature.
Assuming your original table looked like this:

tblTerminals
  TermID, 
  CityName,
  TerminalName

query1:
   SELECT
      Cityname,
      Count(TerminalName) As TermCount
   FROM 
      tblTerminals
   GROUP BY CityName;

Then your spreader takes the results of that query and spreads it by size category:

SELECT 
  CityName,
  IIF(TermCount>0 AND TermCount <= 5, TermCount,0) As TermCount0105,
  IIF(TermCount>5 AND TermCount <= 10, TermCount,0) As TermCount0610,
  IIF(TermCount>0 AND TermCount <= 15, TermCount,0) As TermCount1115,
  IIF(TermCount>0 AND TermCount <= 20, TermCount,0) As TermCount1620,
  IIF(TermCount>20, TermCount,0) As TermCount2199,
FROM 
  (SELECT
  Cityname,
  Count(TerminalName) As TermCount
  FROM tblTerminals
  GROUP BY CityName);

That's easy enough, right?

[JSvar]

This work great. Thank you very much.