Microsoft KB Archive/208669

= ACC2000: How to Use IIf in Crosstab to Limit Column Headings =

Article ID: 208669

Article Last Modified on 7/15/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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

Additional query words: Qrysmp00 exe

Keywords: kbhowto KB208669

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.