Microsoft KB Archive/140910

= ACC: Using IIf in Crosstab to Limit Column Headings (95/97) =

Article ID: 140910

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q140910



Novice: Requires knowledge of the user interface on single-user computers.



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: 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:

182568 ACC97: Microsoft Access 97 Sample Queries Available in Download Center



MORE INFORMATION
The following example uses the IIf function to list companies whose name begins with the letter "A" individually, and to group all other companies under the "OTHERS" column heading:

 Open the sample database Northwind.mdb.  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  Run the query.

