ACC2000: How to Use IIf() in Crosstab to Limit Column Headings

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:

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:

1. Start Microsoft Access and open the sample database Northwind.mdb.
2. Create the following new crosstab query based on the Orders table and the Customers table:

      Query: MyQuery
      ---------------------------------------------------------
      Type: Crosstab query
      Join: Orders.[CustomerID]<->;Customers.[CustomerID]
   
      Field: Country
         Table: Customers
         Total: Group By
         Crosstab: Row Heading
      Field: IIf([CompanyName] Like "A*",[CompanyName],"OTHERS")
         Total: Group By
         Crosstab: Column Heading
      Field: Order ID
         Table: Orders
         Total: Count
         Crosstab: Value
   					

3. Run the query.