---Posted
by Vinod Kumar--- 27/3/2001
How to Group Row Headings in a Crosstab Query
This
article pointer was forwarded to you from the Microsoft Online Support site. http://support.microsoft.com/support/kb/articles/Q208/5/56.ASP
Moderate:
Requires basic macro, coding, and interoperability skills.
This article applies only to a Microsoft Access database (.mdb).
This article describes how to group rows of data using a value range rather than having a single row per value.
CAUTION:
Following the steps in this example will modify the sample database
Northwind.mdb. You may want to back up the Northwind.mdb file and perform 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:
1. Query: SalesInfo
2. ------------------------------------------------------------
3. Type: Select Query
4. Join: Orders.[OrderID] <-> [Order Details Extended].[OrderID]
5.
6. Field: EmployeeID
7. Table: Orders
8. Total: Group By
9. Field: Amount: ExtendedPrice
10. Table: Order Details Extended
11. Total: Sum
12. 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:
13. Query: SalesRanges
14. ----------------------------------------------------
15. Type: Crosstab Query
16.
17. Field: SalesRange: Partition([Amount],0,100000,10000)
18. Total: Group By
19. Crosstab: Row Heading
20. Sort: Ascending
21. Field: Year
22. Total: Group By
23. Crosstab: Column Heading
24. Field: EmployeeID
25. Total: Count
Crosstab: Value
Save the query as SalesRanges.
Run the SalesRanges query. Your results should be similar to the following:
26. SalesRange 1996 1997 1998
27. -------------------------------------------
28. 0: 9999 1
29. 10000: 19999 4 2
30. 20000: 29999 2 1
31. 30000: 39999 1 1
32. 40000: 49999 1 1 3
33. 50000: 59999 1 1
34. 60000: 69999 1 1
35. 70000: 79999 1 2
36. 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:
Q209141 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.