How to Group Column Headings in a Crosstab Query
ID: Q109949
|
The information in this article applies to:
-
Microsoft Access versions 97, 1.0, 1.1, 2.0, 7.0
SUMMARYModerate: Requires basic macro, coding, and interoperability skills.
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:
Q182568 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.
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."
Additional query words:
partition range inf
Keywords : kbdta QryCross
Version : WINDOWS:1.0,1.1,2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto
|