Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

Microsoft Access: How to Group Column Headings in a Crosstab Query


View products that this article applies to.

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

↑ Back to the top


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.

  1. Start Microsoft Access and open the sample database Northwind.mdb.
  2. In the Database window, click Queries, and then click New to create a new query.
  3. In the New Query box, click Design view, and then click OK.
  4. 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.
  5. On the View menu, click Totals to display the Totals row in the grid.
  6. On the Query menu, click Crosstab Query to display the Crosstab row in the grid.
  7. 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.
  8. Run the query.

↑ Back to the top


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.

↑ Back to the top


Article Info
Article ID : 209141
Revision : 3
Created on : 1/1/0001
Published on : 1/1/0001
Exists online : False
Views : 46