Access Cookbook - Ken Getz [22]
Finally, by specifying the TopValues property for the query, you're asking Access to return only that many rows as the result set of the query. If you want a certain percentage of the total rows, change it by adding the % sign after the Top value.
The acbGetRandom function includes a call to the VBA Randomize subroutine. By calling Randomize, you're asking Access to give you a truly random result every time you call the function. If you omit this call, Access gives you the same series of random numbers each time you start it up and run this query. If you want a repeatable series of random rows, remove the call to Randomize. If you want a different set of rows each time you run the query, leave the Randomize statement where it is.
Because Access will pass a field value to the acbGetRandom function for each and every row of data in your data source, you'll want to optimize this function call as much as you can. If possible, use either a very short text field (zip code, for example) or, even better, an integer. You must pass some value, but you want it to be as small as possible to minimize the amount of information that must be moved around for each row of the data.
1.9. 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:
Create a new query based on a table or query containing the appropriate account, date, and amount information.
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.
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, and Figure 1-23 shows the sample data that will be used in this example.
Figure 1-22. The sample query, qryAging, in design mode
For the column headings, group the dates in 30-day increments, using the built-in Partition function. For this specific example, use the following value:
Expr1:Partition(Now( )-[Date],1,120,30)
for the column's expression. This tells the query to break the information into groups based on the difference between today and the field named Date, starting with 1 day old, ending with 120 days old, and breaking every 30 days. Set the Total item to Group By and the Crosstab item to Column Heading.
When you execute the query, you will see output similar to that shown in Figure 1-24, which shows the aging data grouped in 30-day increments. You would usually create a report based on this query, but you can also use this raw output to get an overview of the aging of your receivables.
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