---Posted by Vinod Kumar--- 27/3/2001
How to Use IIf() in Crosstab to Limit Column Headings
This
article pointer was forwarded to you from the Microsoft Online Support site. http://support.microsoft.com/support/kb/articles/Q208/6/69.ASP
Novice:
Requires knowledge of the user interface on single-user computers.
This article applies only to a Microsoft Access database (.mdb).
SUMMARY
You
can use the IIf() function to group values under a small number of
headings in a crosstab query. This article demonstrates how to group records by
country and to count the number of orders placed for each customer.
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
To use the IIf() function to individually list companies whose names begin with the letter "A," and to group all other companies under the OTHERS column heading, follow these steps:
Start Microsoft Access and open the sample database Northwind.mdb.
Create the following new crosstab query based on the Orders table and the Customers table:
1. Query: MyQuery
2. ---------------------------------------------------------
3. Type: Crosstab query
4. Join: Orders.[CustomerID]<->;Customers.[CustomerID]
5.
6. Field: Country
7. Table: Customers
8. Total: Group By
9. Crosstab: Row Heading
10. Field: IIf([CompanyName] Like "A*",[CompanyName],"OTHERS")
11. Total: Group By
12. Crosstab: Column Heading
13. Field: Order ID
14. Table: Orders
15. Total: Count
Crosstab: Value
Run the query.