Part III: More-Advanced Access Techniques
720
Use the Page property to display the current page number in the page header or footer as usual.
For example, include the following expression in an unbound text box in the page footer:= “Page “ & [Me.Page]Unfortunately, it’s not nearly as easy to count the pages within a group so that you could put a
“Page x of y” in the page footer, where y is the number of pages within the group.Formatting Data
In addition to sorting and grouping data, you can make reports more useful by formatting them to
highlight specific information. Numbering the entries or using bullets can make things stand out,
as can using lines or spaces to separate parts of the report. Ensuring that the elements on the report
are positioned in a consistent manner is important as well — you might have all the necessary data
in a report, but poor presentation can leave a very negative impression on the users. The tech-
niques discussed in this section will help you produce reports that are more professional looking.Creating numbered lists
By default, the items contained on an Access report are not numbered. They simply appear in the
order dictated by the settings in the Group, Sort, and Total dialog box.Sometimes it would be useful to have a number assigned to each entry on a report or within a
group on a report. You might need a number to count the items in a list or uniquely identify items
in the list. For example, an order details report might contain an item number for each item
ordered, plus a field for items ordered, showing how many things were ordered.The Access Running Sum feature provides a way to assign a number to each item in a list on an
Access report. For example, the Northwind Traders sales management has asked for a report show-
ing the sum of all purchases by each customer during the month of January, sorted in descending
order so that the top purchaser appears at the top. Oh, yes — and they want a number assigned to
each line in the report to provide a ranking for the Northwind customers.What an assignment! The query to implement this request is shown in Figure 20.14 (qryCu-
stomerPurchasesJanuary). This query sums the purchases by each customer for the month
beginning 1/1/12 and ending 1/31/12. Because the Purchases column is sorted in descending
order, the customers buying the most product will appear at the top of the query results set. The
OrderDate field is not included in the query results and is used only as the query’s selection cri-
terion (notice the Where in the Total row).Although you could do much of this work at runtime using VBA to programmatically sum the val-
ues returned by the query or a SQL statement in the report’s RecordSource property, you
should always let the Access query engine perform aggregate functions. All Access queries are opti-
mized when you save the query. You’re guaranteed that the query will run as fast as possible —
much faster than a filter based on a SQL statement in a report’s RecordSource property.