How to: Use Aggregate Functions to Work with Values in Access SQL

Aggregate functions are used to calculate statistical and summary information from data in tables. These functions are used in SELECT statements, and all of them take fields or expressions as arguments.

To count the number of records in a result set, use the Count function. Using an asterisk with the Count function causes Null values to be counted as well.

  SELECT Count(*) AS [Number of Invoices]
    FROM tblInvoices

To count only non-Null values, use the Count function with a field name:

  SELECT Count(Amount) AS
    [Number of Valid Invoice Amounts]
    FROM tblInvoices

To find the average value for a column or expression of numeric data, use the Avg function:

  SELECT Avg(Amount) AS [Average Invoice Amount]
    FROM tblInvoices

To find the total of the values in a column or expression of numeric data, use the Sum function:

  SELECT Sum(Amount) AS [Total Invoice Amount]
    FROM tblInvoices

To find the minimum value for a column or expression, use the Min function:

  SELECT Min(Amount) AS [Minimum Invoice Amount]
    FROM tblInvoices

To find the maximum value for a column or expression, use the Max function:

  SELECT Max(Amount) AS [Maximum Invoice Amount]
    FROM tblInvoices

To find the first value in a column or expression, use the First function:

  SELECT First(Amount) AS [First Invoice Amount]
    FROM tblInvoices

To find the last value in a column or expression, use the Last function:

  SELECT Last(Amount) AS [Last Invoice Amount] 
    FROM tblInvoices