---Posted by Vinod Kumar--- 27/3/2001

ACC2000: How to Group Column Headings in a Crosstab Query

This article pointer was forwarded to you from the Microsoft Online Support site. http://support.microsoft.com/support/kb/articles/Q209/1/41.ASP

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:

Q207626 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: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform 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.

1.                 Field: Country

2.                    Table: Customers

3.                    Total: Group By

4.                    Crosstab: Row Heading

5.                 Field: Expr1: Switch([CompanyName] Like "[A-Fa-f]*", "A-F", _

6.                               [CompanyName] Like "[G-Og-o]*", "G-O", [CompanyName]_

7.                               Like "[P-Zp-z]*", "P-Z", True, "Other")

8.                    Total: Group By

9.                    Crosstab: Column Heading

10.              Field: OrderID

11.                 Table: Orders

12.                 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.