Access Cookbook - Ken Getz [23]
Figure 1-23. tblAccounts contains sample data to be used in an aging query
Figure 1-24. qryAging shows the aging data grouped in 30-day increments
Discussion
Except for the use of the Partition function, this crosstab query is no different from any other. It summarizes rows of data, summing the amount column, grouped on a range of values in various columns. The only innovation is the use of the Partition function.
The Partition function returns a string indicating where a value occurs within a calculated series of ranges. That string (in the format start:end) becomes the column heading in your query and is based on the starting value, the ending value, and the range size. You tell Access each of these values when you call the Partition function. Table 1-5 shows the four parameters you'll use.
Table 1-5. Parameters for the Partition function
Argument
Description
number
Long integer to evaluate against specified ranges.
start
A long integer: the start of the specified ranges. Can't be less than 0.
stop
A long integer: the end of the specified ranges. Can't be less than the value specified in start.
interval
A long integer: the interval spanned by each range in the series from start to stop. Can't be less than 1.
For example, the following expression:
Partition(42, 1, 120, 30)
would return the value " 31: 60". This function call asks, "Where does the number 42 occur within the range of 1 to 120, broken into 30-day ranges?" Clearly, it falls in the 31- to 60-day range. That's what's indicated by the return value " 31: 60" from the previous example. In doing its calculation, Access formats the result for you, in the format you see in the column headings in Figure 1-25.
Figure 1-25. A simple select query, qryShowAging, using the Partition function
If a value falls outside the requested range, Access returns an open-ended result. For example, the previous case will return "121: " if the value is greater than 120 or " : 0" if the value is less than 1. Access always includes enough space in the two halves of the result string for the largest possible value. This way, the result strings will sort correctly.
To see the Partition function doing its work, open the query qryShowAging from 01-09.MDB in design mode (see Figure 1-25). This simple select query will show the account number, the amount due, the date on which the transaction occurred, and the age range into which the transaction fits, using the Partition function to calculate the ranges. Figure 1-26 shows the same query in datasheet view, using the data as shown in Figure 1-23. The last column of the datasheet shows the output from the Partition function. When you group the rows on the values in this column, you end up with the crosstab query you created earlier in this section.
Figure 1-26. Rows returned by qryShowAging
There are some limitations to the Partition function. If you want uneven partitions, you'll need to write your own VBA function to do the work. For example, if you want your partitions to be 0-30 days, 31-60 days, 61-90 days, and 91-120 days, you'd be out of luck with the Partition function: all the partitions specified are 30 days except the first, which is 31. In addition, using Partition in a crosstab query will omit ranges for which no values exist. For example, if no account has transactions between 31 and 60 days ago, there will be no column for this range in the output query. To avoid this problem, use fixed column headings (see the Solution in Recipe 1.4).
See Also
For more information on the Partition function, search on "Partition Function" in Access' online help.
1.10. Create a Join That's Based on a Comparison Other than Equality
Problem
You need to join together two tables in a query on the Between operator. For example, you have a table of students and their grades, and a table of grade ranges and