Microsoft KB Archive/120883

= ACC: Using IIf in Crosstab to Limit Column Headings (1.x/2.0) =

Article ID: 120883

Article Last Modified on 5/6/2003

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q120883



Moderate: Requires basic macro, coding, and interoperability skills.



SUMMARY
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.

