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

• Microsoft Access versions 7.0, 97

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.

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:

   ARTICLE-ID: Q140910
   TITLE:      ACC2: Using IIf() in Crosstab to Limit Column Headings