Microsoft Office Tutorials and References
In Depth Information
Adding Subtotals and Totals from Subforms
Summarizing lots of records
In addition to the functions that work with
the field values in the current record, Access
has domain aggregate functions — functions
that work with field values in some or all the
records in a table or query. ( Domain is a fancy
name for table or query. ) You may want a form
to display the grand total of all the orders so
far this year or the amount of the largest order
placed, for example. To total the value of a field
for a bunch of records, you use the DSum
function, which has this syntax:
DSum( expression , domain ,
criterion )
Replace expression with the field name
that you want to total (or an expression such
as [Price] * [Qty] ), in quotes. Replace
domain with the table or query name, in
quotes. Optionally, you can include a
criterion that limits which records to include.
The following example expression totals the
extended price ( Price × Qty ) for all the
records in the Order Details table:
DSum(“[Price] * [Qty]”, “Order
Following are some of the other domain
aggregate functions you can use (all of which have
the same syntax as DSum ):
DAvg Averages the values. :
DCount Counts the values. :
DFirst Displays the value of the first :
DLast Displays the value of the last :
DMin Displays the minimum value. (For :
numbers, this value is the smallest; for text,
it’s the first in alphabetical order; for dates,
it’s the earliest.)
DMax Displays the maximum value. (For :
numbers, this value is the largest; for text,
it’s the last in alphabetical order; for dates,
it’s the latest.)
One other useful domain aggregate function
is DLookup , which returns the value of a
specific field for a specific record in a table
or query. The following expression returns the
date OrderID 5000 from the Orders table:
DLookup(“[Order Date]”,
“Orders”, “[OrderID] =
In this DLookup function, the expression is
“[Order Date]” — the date of the order.
The domain is the Orders table. The criterion —
“[OrderID] = 5000” — limits the
records to include only the record with that
specific ID.
Suppose that you have an Orders form and an Order Details form that
lists the items included in the order. The total of the Qty field in the Order
Details subform would be useful to tell the shipping clerk how many items
need to be shipped for this order. The expression is
= Sum([Qty])
If a field name contains spaces, you have to enclose it in square brackets. We
enclose all field names in square brackets just to be safe.
Search JabSto ::

Custom Search