Access Aggregate Function
Dlookup DMin DMax DAvg DCount Dfirst Dlast
Access Aggregate Functions are used in Access Visual Basic,
Access form field calculations and Access SQL queries.
The Dlookup aggregate function is probably the most widely used
function and can be combined with the other
functions to perform powerful calculations.

DLookup  get the value of a field from a particular
record  similar to Microsoft VLookup function

DMin  retrieve the minimum value in a specified set of
records

DMax  obtain the maximum values in a specified set of
records

DAvg  calculate the average of a set of values in a
set of records

DFirst  get first value in a table based on the order by clause

DLast  get last value in the table based on the order by clause

DCount  determine the count of a set of records

DSum  calculate the sum of a set of values in a
specified set of records

DStDev & DStDevP  estimate the standard deviation
across a set of values in a specified set of records; DStDevP  for
population estimates and DStDev for sample estimates.

DVar & DvarP  estimates the variance for a sample (DVar)
or population.
VBA Aggregate functions provide a powerful means
to do otherwise complex, time consuming statistical analysis programming on forms and reports.
However, over use of these functions on forms and large reports can
dramatically slow down an Access database  especially in a client/server
setup (back end/front end). You can combine these functions to extend
Access's statistical analysis capabilities to perform many statistical measures
such as correlation coefficients and analysis of variance.
Aggregate functions can be implemented as:
But don't use macros! Use the left side menu to
go to these examples listed below. Note that the syntax for the domain aggregate
functions is the same for both visual basic and SQL aggregate functions.
Access Aggregate Functions
Access Dlookup Example 
Dynamic lookup lists provide for standard
selections of text data. Data integrity and speed of data entry is
sign ficantly enhanced by the use of the DLookup feature. We recommend
that you make extensive use of them in the design of your data entry forms. 
Compound Dynamic Lookup Code 
This example shows you how to nest dynamic
lookups to get the lookup of the results of another lookup. The
nesting syntax can become somewhat complicated. 
Minimum & Maximum Function Examples 
Determining the minimum and maximum values of a recordset is done using the min and max
aggregate functions. See examples of these two functions in this
example. 
Find Group & Population Standard Deviation 
The few statistical measure provide in Access
SQL through the aggregate functions provide the basis for calculating more
complex functions such as analysis of variance and correlation coefficients. 