Sum Function

Returns the sum of a set of values contained in a specified field on a query.



The expr placeholder represents a string expression (string expression: An expression that evaluates to a sequence of contiguous characters. Elements of the expression can be: functions that return a string or a string Variant (VarType 8); a string literal, constant, variable, or Variant.) identifying the field that contains the numeric data you want to add or an expression that performs a calculation using the data in that field. Operands in expr can include the name of a table field, a constant, or a function (which can be either intrinsic or user-defined but not one of the other SQL aggregate functions).


The Sum function totals the values in a field. For example, you could use the Sum function to determine the total cost of freight charges.

The Sum function ignores records that contain Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain a Null value.) fields. The following example shows how you can calculate the sum of the products of UnitPrice and Quantity fields:

SELECT Sum(UnitPrice * Quantity) AS [Total Revenue]
FROM [Order Details];

You can use the Sum function in a query expression. You can also use this expression in the SQL property of a QueryDef (QueryDef: A stored definition of a query in an Access database, or a temporary definition of a query in an ODBCDirect workspace.) object or when creating a Recordset (recordset: The collective name given to table-, dynaset-, and snapshot-type Recordset objects, which are sets of records that behave as objects.) based on an SQL query.

Applies to:
Access 2007