Access Cookbook

Create a Query That Will Show Aging of Receivables

Problem

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.

Solution

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:

1. Create a new query based on a table or query containing the appropriate account, date, and amount information.

2. Convert this query to a crosstab query by choosing the Query → Crosstab menu item or by clicking on the Crosstab button on the Query Design toolbar.

3. As when you create any crosstab query, specify at least three columns in the query grid: one for the column headings, one for the row headings, and one for the values that make up the crosstab. In this case, choose the account number (or account name, depending on your data) as the Row Heading and the amount (summed) as the Value. Figure 1-22 shows the sample query in design mode.

   

   Figure 1-22. The sample query, qryAging, in design ...