The information in this article applies to:
- Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.
This article describes how to group values in a crosstab query using a
fixed number of headings, rather than having a column for every value.
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.
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."
|