Microsoft KB Archive/208556

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

Article ID: 208556

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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.  Open the sample database Northwind.mdb.  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                    Close the query, and then save it 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: <pre class="fixed_text">   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                          </li></ol>

<div class="references_section">