Recipe 1.9 Create a Query That Will Show Aging of Receivables
Using a crosstab query, you need to age transactions, grouped by Account ID, into ranges of 1-30 days, 31-60 days, 61-90 days, and greater than 120 days. You know that you can group transactions by month using the standard query tools, but you can't find a way to group them by 30-day increments.
Access provides the seldom-used Partition function, which is perfect for this task. It allows you to take a range of values and partition it into even-sized chunks. By specifying a 30-day partition size, you can create a crosstab query that will give you the information you need.
To create a query in your own application, follow these steps:
Figure 1-22. The sample query, qryAging, in design mode
To see an example of a query that shows the aging of receivables, load the sample database, 01-09.MDB. This database includes a simple table, tblAccounts (see Figure 1-23), containing sample data about accounts and their activity to be used in an aging query. The query qryAging, shown in Figure 1-24, shows the final outcome: a crosstab query including the aging information, grouped in 30-day increments. You may want to update this small table with dates that are closer to the date on which you are testing it.
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
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.
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).
1.9.4 See Also
For more information on the Partition function, search on "Partition Function" in Access' online help.