Microsoft Office Tutorials and References
In Depth Information
Adding Subtotals and Totals from Subforms
Adding Subtotals and Totals from Subforms
If your form includes a subform (or if your report includes a subreport), and
the information shown in the subform includes quantities, you may want to
display a total on the main form. On an Orders form that contains an Order
Details subform, the main form can include the total cost of all the items in
the subform and maybe a count of the records in the subform. Figure 4-7,
earlier in this chapter, shows an Orders form with a subform listing the
items that the customer is buying.
Unfortunately, you can’t make a control on the main form that calculates a
total for the records on the subform. You can, however, make a control on
the subform that calculates the total and then make a control on the main
form that displays the value of this control. This process seems like an extra
step to us, but it works. The following sections cover what you need to know
to create totals and counts of subform records.
Using aggregate functions
An aggregate function is a function that combines a bunch of values. The
Sum() function, for example, adds a bunch of numbers together. (Simple
enough!) When doing calculations based on a bunch of records, you can use
the aggregate functions outlined in the following table.
Function
Description
Sum()
Totals the values.
Count()
Counts the values.
Averages the values (sum divided by count).
Avg()
Calculates the smallest value (for numeric values), the
earliest date (for date values), or the first value in alphabetical
order (for text values).
Min()
Max()
Calculates the largest value (for numeric values), the latest
date (for date values), or the last value in alphabetical
order (for text values).
Book IV
Chapter 4
First()
Uses the value from the first record.
Last()
Uses the value from the last record.
Aggregate functions work only when Access knows what set of records you
want to work with. On forms, they work in the form footer of a subform. (See
Chapter 3 of this minibook for a description of a form footer unless you’ve
already guessed that a form footer is a section that appears at the bottom of
a form.)

Search JabSto ::

Custom Search