Access Aggregate Functions

 

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

 

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