Partition Function

Description

Returns a string indicating where a number occurs within a calculated series of ranges.

Syntax

Partition(number, start, stop, interval)

The Partition function syntax has these named arguments:

Remarks

The Partition function identifies the particular range in which number falls and returns a string describing that range. The Partition function is most useful in queries. You can create a select query that shows how many orders fall within various ranges (for example, order values from 1 to 1000, 1001 to 2000, and so on).

The following table shows how the ranges are determined using three sets of start, stop, and interval parts. The First Range and Last Range columns show what Partition returns. The ranges are represented by lowervalue:uppervalue, where the low end (lowervalue) of the range is separated from the high end (uppervalue) with a colon ( : ).

In the table shown above, the third line shows the result when start and stop define a set of numbers that can’t be evenly divided by interval. The last range extends to stop (11 numbers) even though interval is 20.

If necessary, Partition returns a range with enough leading spaces so that there are the same number of characters to the left and right of the colon as there are characters in stop, plus one. This ensures that if you use Partition with other numbers, the resulting text will be handled properly during any subsequent sort operation.

If interval is 1, the range is number:number, regardless of the start and stop arguments. For example, if interval is 1, number is 100 and stop is 1000, Partition returns “ 100: 100”.

If any of the parts is Null, Partition returns a Null.

Example

This example assumes you have an Orders table that contains a Freight field. It creates a select that counts the number of orders for which freight cost falls into each of several ranges. The Partition function is used first to establish these ranges, then the SQL Count function counts the number of orders in each range. In this example, the arguments to the Partition function are start = 0, stop = 500, interval = 50. The first range would therefore be 0:49, and so on up to 500

SELECT DISTINCTROW Partition([freight],0, 500, 50) AS Range,(Orders.Freight) AS CountOrdersBY Partition([freight],0,500,50);

You can use the Partition function in the query design grid as well as in the SQL view of the Query window. In the query design grid, you can use the Partition function in a calculated field or to specify criteria for a select query.

The following example shows how you can use the Partition function to create a calculated field that lists how many records fall into each specified range. Suppose you have an Orders table that contains a Freight field. In the Query window, create a new Totals query by adding the Orders table and clicking on the Totals button in the Query toolbar. Drag the Freight field to the first Field cell on the query design grid, and set the value of the Total cell to Count. In another field cell, enter the following expression.

Range: Partition([Freight], 0, 1000, 50)

Set the Total cell below this field to Group By, and run the query. The Partition function returns eleven ranges (0:99, 100:199, 200:299, and so on). The query shows the number of orders with freight charges falling into each range.

The next example shows how you can use the Partition function in the SQL view of the Query window. It creates a crosstab query that evaluates a Freight field in an Orders table. It calculates the number of orders for each customer for which freight cost falls within one of several ranges. The ranges are defined by the arguments to the Partition function: start = 0, stop = 1000, interval = 50.

Enter the following expression in SQL view. When you run this query, each range will appear as a column heading.

TRANSFORM Count(Orders.[OrderID]) AS [CountOfOrderID]Orders.[CustomerID]OrdersBY Orders.[CustomerID]Partition(Int([Freight]), 0, 1000, 50);