Access Aggregate Function

Dlookup DMin DMax DAvg DCount Dfirst Dlast

Access Aggregate Function 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.

Access 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:

SQL Aggregate Functions
VBA Aggregate Functions
Macro Aggregate Functions

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 signficantly 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.