ACC: Using IIf() in Crosstab to Limit Column Headings (1.x/2.0)
ID: Q120883
|
The information in this article applies to:
-
Microsoft Access versions 1.0, 1.1, 2.0
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.
You can use the IIf() function to group values under a small number of
headings in a crosstab query. This article contains an example
demonstrating how to group records by country and count the number of
orders placed for each customer.
MORE INFORMATION
The following example uses the IIf() function to list companies whose name
begins with the letter "A" individually, and group all other companies
under the "OTHERS" column heading:
- Open the sample database NWIND.MDB.
- Create the following new crosstab query based on the Orders and
Customers tables:
Query: MyQuery
-----------------------------------------------------------------
Type: crosstab query
Join: Orders.[Customer ID]<->Customers.[Customer ID]
Field Name: Country
Table Name: Customers
Total: Group By
Crosstab: Row Heading
Field Name: IIf([Company Name] Like "A*",[Company Name],"OTHERS")
Total: Group By
Crosstab: Column Heading
Field Name: Order ID
Table Name: Orders
Total: Count
Crosstab: Value
- Run the query.
Note that you could use this technique to list data for the current month
in one column, and the sum of prior months' data in another.
REFERENCES
For information about this same topic in Microsoft Access 95 and 97,
please see the following article here in the Microsoft Knowledge base:
Q140910 ACC: Using IIf() in Crosstab to Limit Column Headings
For more information about the IIf() function, search for "IIf," and then
"IIf Function" using the Microsoft Access Help menu.
Microsoft Access "User's Guide," version 1.0, Chapter 6, "Designing Select
Queries," pages 150-155
Microsoft Access "User's Guide," version 1.1, Chapter 6, "Designing Select
Queries," pages 154-159
Microsoft Access "User's Guide," version 2.0, Chapter 12, "Advanced
Queries," pages 279-285
Microsoft Access "Language Reference," version 1.0, pages 248-249
Additional query words:
iif fixed
Keywords : kbusage QryCross
Version : 1.0 1.1 2.0
Platform : WINDOWS
Issue type : kbinfo