Article ID: 143387 - Last Review: January 19, 2007 - Revision: 2.1

ACC: How to Group Row Headings in a Crosstab Query

Retired KB ArticleRetired KB Content Disclaimer
View products that this article applies to.
This article was previously published under Q143387
Moderate: Requires basic macro, coding, and interoperability skills.
Expand all | Collapse all

SUMMARY

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

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
Back to the top

REFERENCES

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

109949  (http://support.microsoft.com/kb/109949/EN-US/ ) ACC: How to Group Column Headings in a Crosstab Query

For more information about creating crosstab queries, search the Help Index for "crosstab," or ask the Microsoft Access 97 Office Assistant.

For more information about the Partition() function, search the Help Index for "Partition function."
Back to the top
APPLIES TO
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition
Back to the top
Keywords: 
kbhowto KB143387
Back to the top
Retired KB ArticleRetired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Back to the top
 

Get Help Now

Contact a support professional by E-mail, Online, or Phone

Article Translations