ACC2000: How to Group Row Headings in a Crosstab Query (208556)


The information in this article applies to:

  • Microsoft Access 2000
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).

SUMMARY

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

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

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.
Modification Type:MajorLast Reviewed:6/24/2004
Keywords:kbhowto kbinfo KB208556
©2004 Microsoft Corporation. All rights reserved.