Microsoft KB Archive/143387

= ACC: How to Group Row Headings in a Crosstab Query =

Article ID: 143387

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q143387



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



SUMMARY
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.

 Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).  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.  Close and save the query as SalesInfo.  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  Save the query as SalesRanges.</li>  Run the SalesRanges query. Your results should be similar to the following: (NOTE: This data is from Microsoft Access 97)

<pre class="fixed_text">     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                        </li></ol>

<div class="references_section">