Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: How to Group Row Headings in a Crosstab Query

ACC2000: How to Group Row Headings in a Crosstab Query

View products that this article applies to.

This article was previously published under Q208556
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

↑ Back to the top

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

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

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.
  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
  3. Close the query, and then save it 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:
        SalesRange   	1996	1997	1998	
    -------------------------------------------
        0:  9999	 1			
    10000: 19999	 4		 2	
    20000: 29999	 2	 1		
    30000: 39999	 1	 1		
    40000: 49999	 1	 1	 3	
    50000: 59999		 1	 1	
    60000: 69999		 1	 1	
    70000: 79999		 1	 2	
    90000: 99999		 1		
    100001:      		 2

↑ Back to the top

References

For additional information about grouping column headings in a crosstab query, click the article number below to view the article in the Microsoft Knowledge Base:
209141� ACC2000: How to Group Column Headings in a Crosstab Query
For more information about creating crosstab queries, click Microsoft Access Help on the Help menu, type create a query in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about the Partition() function, click Microsoft Access Help on the Help menu, type partition function example(mdb) in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

↑ Back to the top

Keywords: KB208556, kbinfo, kbhowto

↑ Back to the top

Article Info
Article ID : 208556
Revision : 2
Created on : 6/24/2004
Published on : 6/24/2004
Exists online : False
Views : 327