Article ID: 109949 - Last Review: January 18, 2007 - Revision: 2.1

How to Group Column Headings in a Crosstab Query

Retired KB ArticleRetired KB Content Disclaimer
View products that this article applies to.
This article was previously published under Q109949
Moderate: Requires basic macro, coding, and interoperability skills.

Expand all | Collapse all

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  (http://support.microsoft.com/kb/182568/EN-US/ ) ACC97: Microsoft Access 97 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.
  1. Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x or 2.0).
  2. 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.
  3. Run the query.
Back to the top

REFERENCES

For more information about crosstab queries, search the Help Index for "crosstab queries," or ask the Microsoft Access 97 Office Assistant.

For more information about the Switch() function, search the Help Index for "Switch Function."
Back to the top
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
Back to the top
Keywords: 
kbhowto KB109949
Back to the top
Retired KB ArticleRetired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Back to the top
 

Get Help Now

Contact a support professional by E-mail, Online, or Phone

Article Translations