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.
Microsoft Access is a fully capable database and it's primary limitations is the number of concurrent users it will support. Generally, we recommend a limit of 10 to 15 users.
Did you know you don't need to move to SQL/Server to run and manage your Access database on the net. There are many alternative solutions. Among them are Amazon's AWS cloud services and various hosting services in nearly every state.
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
DMax - obtain the maximum values in a specified set of
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)
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
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.