<%index=1 crumb1="Aggregate Functions in Access" %> Access Aggregate Functions
Home  Fees/Services  Access Examples  Tutorials  Access Download  Articles  Search  Contact  Privacy  Links
<% response.write crumb1 %> <% response.write crumb2 %>
Home > MS Access Tutorials > VBA Aggregate Functions<% response.write " > " & crumb %>

Dlookup Function Example
Compound Dlookup Code
DMin & DMax Examples
DStDev & DStDevP Function


Microsoft Office VBA :
  MS Access 2003
  Access 2007
  Access 2010
  Access 2013
  Access 2016

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:

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

Contact Information

<% Response.write "Copyright 2000-" & year(now) & " Blue Claw Database Design" %>