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



The information in this article applies to:

  • Microsoft Access 1.0
  • Microsoft Access 1.1
  • Microsoft Access 2.0

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

REFERENCES

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

140910 ACC: Using IIf() in Crosstab to Limit Column Headings

For more information about the IIf() function, search for "IIf," and then "IIf Function" using the Microsoft Access Help menu.

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

Modification Type:MajorLast Reviewed:5/6/2003
Keywords:kbinfo kbusage KB120883