ACC: How to Group Row Headings in a Crosstab Query

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

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

This article describes how to group rows of data using a value range, rather than having a single row per value.

MORE INFORMATION

The following example uses a crosstab query (based on a select query) with the Partition() function to indicate where a number occurs within a calculated series of ranges.

1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).

2. Create the following query based on the Orders table and the Order Details Extended query:

         Query: SalesInfo
         ------------------------------------------------------------
         Type: Select Query
         Join: Orders.[OrderID] <-> [Order Details Extended].[OrderID]
   

         Field: EmployeeID
            Table: Orders
            Total: Group By
         Field: Amount: ExtendedPrice
            Table: Order Details Extended
            Total: Sum
         Field: Year: Year([OrderDate])
            Total: Group By
   
      NOTE: In version 2.0, there is a space in the following field names:
      Employee ID, Extended Price, Order ID, and Order Date.
   
   

3. Close and save the query as SalesInfo.

4. Create the following query based on the SalesInfo query:

         Query: SalesRanges
         ----------------------------------------------------
         Type: Crosstab Query
   

         Field: SalesRange: Partition([Amount],0,100000,10000)
            Total: Group By
            Crosstab: Row Heading
            Sort: Ascending
         Field: Year
            Total: Group By
            Crosstab: Column Heading
         Field: EmployeeID
            Total: Count
            Crosstab: Value
   
   

5. Save the query as SalesRanges.

6. Run the SalesRanges query. Your results should be similar to the following: (NOTE: This data is from Microsoft Access 97)

         SalesRange       1994   1995   1996
         -----------------------------------
              0:  9999       2
          10000: 19999       5
          20000: 29999       1      1      2
          40000: 49999       1      2      2
          50000: 59999              2      1
          60000: 69999              1
          70000: 79999                     2
          80000: 89999              1      1
          90000: 99999              1      1
         100001:                    1
   
   

REFERENCES

For information about grouping column headings in a crosstab query, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q109949
   TITLE     : ACC: How to Group Column Headings in a Crosstab Query