ACC2000: How to Group Column Headings in a Crosstab Query (209141)

SUMMARY

This article describes how to group values in a crosstab query by using a fixed number of headings, rather than by having a column for every value.

NOTE: You can see a demonstration of the technique that is used in this article in the sample file Qrysmp00.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

207626 ACC2000: Access 2000 Sample Queries Available in Download Center

MORE INFORMATION

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
						
```
Note that both "A-F" and "a-f" are required in the Switch() function's argument because the argument is case-sensitive. The "True" in the argument acts like an Else condition, collecting data that does not match any of the previous conditions.
Run the query.

REFERENCES

For more information about crosstab queries, click Microsoft Access Help on the Help menu, type create a crosstab report with fixed-column headings in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about the switch function, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type switch function example in the Office Assistant or the Answer Wizard, and then click Search to view the topic.