Crosstab query with 3 main rows/colums + "Other"?

[Geo]

I would like to get a result showing the number of journeys involving 3
particlar locations with any other place lumped in to a single column e.g:-
_______Manchester London Birmingham Other
Manchester
London
Birmingham
Other

I currently have a select query (selecting only journeys with the three
locations as start and/or finish) and a crosstab query which gives me all the
rows/columns for hundreds of locations but am only interested in displaying a
particular group of 3.
Should I somehow create a temporary table from the select query and use code to
null any field not containing one of the 3 locations?

Geo

 

[John Spencer]

You might be able to use a UNION query as the source of the crosstab.
SELECT City
FROM YourTable
WHERE City in ("Manchester","London","Birmingham")
UNION ALL
SELECT "Other" as City
FROM YourTable
WHERE City Not in ("Manchester","London","Birmingham")

OR you could use an IIF in the query

Field: CityName: IIF(City in ("Manchester","London","Birmingham"),City,"Other")

If you are doing this in a crosstab, you should be able to modify the Pivot
clause of the SQL to read

PIVOT IIF([City] in ("Manchester","London","Birmingham"),[City],"Other")

If you are doing this in design view (grid)
Field: CityName: IIF([TableName].[CityField] in
"Manchester","London","Birmingham"),[TableName].[CityField],"Other")
Table: <Blank>
Total: Group By
Crosstab: Column Heading

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Geo]

That was a quick response John!

The quickest modification to make looked like your IIF statement:-

Field: CityName: IIF(City in ("Manchester","London","Birmingham"),City,"Other")

which I inserted in two fields of the select query. Changed the crosstab to use
these fields and the result was EXACTLY what I needed.

I will try the other methods as a learning exercise since you took the time to
type them.

Many thanks John (from a warm and sunny Scotland)


Geo