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


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q208669
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:

207626 ACC2000: Access 2000 Sample Queries Available in Download Center

MORE INFORMATION

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.
Modification Type:MinorLast Reviewed:7/15/2004
Keywords:kbhowto KB208669
©2004 Microsoft Corporation. All rights reserved.