Partition Function Example 3

This example creates a crosstab query that inspects the Order Amount field in an Orders table.  It calculates the number of orders for each customer falling within each of several ranges.  The ranges are defined by the arguments to Partition:

start = 1, stop = 20000, interval = 1000.

You can use the following SQL statement for this example:

TRANSFORM Count(Orders.[Order ID]) AS [CountOfOrder ID]
SELECT Orders.[Customer ID]
FROM Orders
GROUP BY Orders.[Customer ID]
PIVOT Partition(Int([Order Amount]), 1, 20000, 1000);

The result of the crosstab query could look like the following table:

Customer ID       1: 1000  1001: 2000   2001: 3000     3001: 4000

ALWAO       7                        

ANDRC       4                        

ANTHB       10  2          1          

AROUT       9   3          1          1

BABUJ       10  7          2          

BERGS       8   3                     

BLUEL       2   5          1          

BLUMG       1             1          

: