DSum() and DAvg() Functions MS Access

In Microsoft Access, domain aggregate functions like DSum() and DAvg() provide a powerful way to calculate summaries of data across tables or queries. These functions allow users to perform calculations dynamically based on specific criteria, making them particularly useful in forms, reports, and queries. In this article, We will learn about The DSum() and DAvg() Functions of MS Access by understanding their examples.

DSum() Function

• The DSum() function is primarily used in Microsoft Access.
• It calculates the sum of a set of values from a specified field in a domain (which can be a table or query) based on a given criteria.
• This function is often used in Access queries, forms and reports to calculate totals dynamically.

Syntax:

DSum (expr, domain [, criteria])

Parameters:

1. expr: The field or expression to sum. This could be a field name or a calculation based on fields.
2. domain: The set of records (table or query) from which to retrieve the values.
3. criteria (optional): A condition that limits the data to be included in the sum. This is similar to a SQL WHERE clause.

Returns:

It returns sum of all values in a specified set of records which satisfies the criteria. If no record satisfies the criteria argument or if domain contains no records, the DSum function returns a Null.

Examples of DSum() Function in MS Access

To understand the DSum() Function in MS Access we will see the below table called Product Details as shown below:

Example 1:

Finding the sum of all product price.

Select DSum("Product_Price", "Product Details") as Total_Price;

Output:

Example 2:

Finding the sum of product price for a given condition where the product id is less than 103.

Select DSum("Product_Price", "Product Details", "Product_Id  < 103") as Total_Price;

Output:

DAvg() Function

• The DAvg() function is a domain aggregate function in Microsoft Access used to calculate the average of a set of values in a specified field (or expression) over a domain (such as a table or query).
• It can be useful when you want to find the average value of records that match specific criteria.

Syntax:

DAvg (expr, domain [, criteria])

Parameter:

• expression: The field (or expression) for which you want the average value.
• domain: The set of records (e.g., a table or query) where the calculation will be performed.
• criteria (optional): An optional string that specifies the conditions to filter records before calculating the average.

Returns:

It returns average of all values in a specified set of records which satisfies the criteria. If no record satisfies the criteria argument DAvg function returns a Null.

Examples of DAvg() Function in MS Access

Table – Product Details :

Example 1:

Finding the average of product price.

Select DAvg("Product_Price", "Product Details") as Avg_Price;

Output:

Example 2:

Finding the average of product price for a given condition where the product id is less than 103.

Select DAvg("Product_Price", "Product Details", "Product_Id  < 103") as Avg_Price;

Output:

DSum() and DAvg() Functions MS Access

Here’s a comparison between the DSum() and DAvg() functions in Microsoft Access in table format:

Conclusion

The DSum() and DAvg() functions in Microsoft Access are essential tools for users who need to calculate totals and averages dynamically within a database. By applying specific criteria, you can target particular subsets of data, making your queries, forms, and reports more efficient and insightful.

jana_sayantan

Follow

Improve

Article Tags :

• Databases
• SQL
• DBMS-SQL