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

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:

Open the sample database NWIND.MDB.

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

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