Microsoft KB Archive/109949

= How to Group Column Headings in a Crosstab Query =

Article ID: 109949

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q109949



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



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: A demonstration of the technique used in this article can be seen in the sample file, Qrysmp97.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

182568 ACC97: Microsoft Access 97 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.

 Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x or 2.0).  Create the following query based on the Orders and Customers tables.

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.

     Query: Switch Limits Headings ---     Type: Crosstab Query Join: Orders.[CustomerID] <-> Customers.[CustomerID]

NOTE: In versions 1.x and 2.0, there is a space in Customer ID.

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")

NOTE: In versions 1.x and 2.0, there is a space in Company Name.

Total: Group By        Crosstab: Column Heading Field: OrderID Table: Orders Total: Count Crosstab: Value

NOTE: In versions 1.x and 2.0, there is a space in Order ID.

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.

