Microsoft KB Archive/209141

= ACC2000: How to Group Column Headings in a Crosstab Query =

Article ID: 209141

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209141



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



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.</li>  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. <pre class="fixed_text">  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. </li> Run the query.</li></ol>

<div class="references_section">