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

• Microsoft Access versions 1.0, 1.1, 2.0

SUMMARY

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

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:

1. Open the sample database NWIND.MDB.

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

3. Run the query.

REFERENCES

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

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

Microsoft Access "User's Guide," version 1.0, Chapter 6, "Designing Select Queries," pages 150-155

Microsoft Access "User's Guide," version 1.1, Chapter 6, "Designing Select Queries," pages 154-159

Microsoft Access "User's Guide," version 2.0, Chapter 12, "Advanced Queries," pages 279-285

Microsoft Access "Language Reference," version 1.0, pages 248-249