Microsoft Access 2010 Bible

Part III: More-Advanced Access Techniques

722

Adding a Ranking column to the simple report you see in Figure 20.15 is not difficult. Although

the information that’s shown in Figure 20.15 is useful, it’s not what the user asked for.

To add a Ranking column to the report, use the RunningSum property of an unbound text box to

sum its own value over each item in the report. When the RunningSum property is set to Over

Group, Access adds 1 to the value in this text box for each record displayed in the Detail section

of the report (RunningSum can also be used within a group header or footer). The alternate set-

ting (Over All) instructs Access to add 1 each time the text box appears in the entire report. Add

an unbound text box to the left of the CompanyName text box on the report, with an appropriate

header in the Page Header area. Set the RecordSource property for the text box to =1 and the

RunningSum property to Over All. Figure 20.16 shows how the Rank text box is set up on

rptNumberedList.

FIGURE 20.16

The value in the unbound text box named txtRank will be incremented by 1 for each record in the
report.

When this report (rptNumberedList) is run, the Rank column is filled with the running sum

calculated by Access (see Figure 20.17). Once again, the data in this report is the same as in other

report examples. The main difference is the amount of manipulation done by the query before the

data arrives at the report and the additional information provided by the running sum.

Reports can contain multiple running sum fields. You could, for example, keep a running sum to

show the number of items packed in each box of a multiple-box order while another running sum

counts the number of boxes. The running sum starts at 0 (zero), hence the need to initialize it to 1

in the Control Source property on the Property Sheet.

You can also assign a running sum within each group by setting the RunningSum property of the

unbound text box to Over Group instead of Over All. In this case, the running sum will start at

zero for each group. So, be sure to set the ControlSource property of a group’s running sum to 1.