Microsoft Access 2010 Bible

Part III: More-Advanced Access Techniques

748

The built-in Pages variable (which contains the total number of pages in the report) isn’t deter-

mined until Access has completed the first pass through the report. On the second pass, Access has

a valid number to use in place of the Pages variable.

The biggest advantage of two-pass reporting is that you’re free to use aggregate functions that

depend on the report’s underlying record source. Group headers and footers can include informa-

tion that can’t be known until the entire record source is processed.

There are many situations where aggregate information provides valuable insight into data analysis.

Consider a report that must contain each salesperson’s performance over the last year measured

against the total sales for the sales organization, or a region’s sales figures against sales for the entire

sales area. A bookstore might want to know what portion of its inventory is devoted to each book

category.

Figure 20.41 shows such a report. The Number of Customers, Total Sales, and Average Purchase

information at the top of this report (rptSummary) are all part of the report header. In a one-pass

report writer, the data needed to perform these calculations would not appear until the bottom of

the page, after all the records have been processed and laid out.

A glance at rptSummary in Design view (see Figure 20.42) reveals that the text boxes in the

report header are populated with data derived from these mathematical expressions:

Number of Customers: =Count([CompanyName])

Total Sales: =Format(Sum([Purchases]),”Currency”)

Average Purchase: =Format(Sum([Purchases])/ _

Count([CompanyName]), “Currency”)

FIGURE 20.41

The summary information is part of the report’s header.