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

ID: Q140910

The information in this article applies to:

SUMMARY

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

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:

Q182568 ACC97: Microsoft Access 97 Sample Queries Available on MSL


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:

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



REFERENCES

For information about this same topic in Microsoft Access 2.0, please see the following article here in the Microsoft Knowledge base:

Q140910 ACC2: Using IIf() in Crosstab to Limit Column Headings

For more information about the IIF() function search the Help Index for "IIF Function." 


Keywords          : QryCross 
Version           : 7.0 97
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: March 24, 1999