The following example demonstrates how to use the
Switch() function to group non-numeric values under four fixed headings. The headings are
"A-F," "G-O," "P-Z," and "Other."
CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.
- Start Microsoft Access and open the sample database Northwind.mdb.
- In the Database window, click Queries, and then click New to create a new query.
- In the New Query box, click Design view, and then click OK.
- In the Show Table dialog box, add the Customers table and the Orders table to the query grid by double-clicking on Customers, double-clicking on Orders, and then clicking Close.
- On the View menu, click Totals to display the Totals row in the grid.
- On the Query menu, click Crosstab Query to display the Crosstab row in the grid.
- Complete the query grid, using the following specifications:
NOTE: In the following sample expressions, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating these expressions.
Field: Country
Table: Customers
Total: Group By
Crosstab: Row Heading
Field: Expr1: Switch([CompanyName] Like "[A-Fa-f]*", "A-F", _
[CompanyName] Like "[G-Og-o]*", "G-O", [CompanyName]_
Like "[P-Zp-z]*", "P-Z", True, "Other")
Total: Group By
Crosstab: Column Heading
Field: OrderID
Table: Orders
Total: Count
Crosstab: Value
- Run the query.