Open In App

DSum() and DAvg() Functions MS Access

Improve
Improve
Like Article
Like
Save
Share
Report

1. DSum() Function :
In MS Access, the DSum() function is used to calculate the sum of a set of values in a specified set of records (a domain). The DSum functions return the sum of a set of values from a field that satisfy the criteria. The difference between DSum and Sum is that in DSum function, values are calculated before data is grouped and in the case of Sum function, the data is grouped before values in the field expression are evaluated.

Syntax :

DSum (expr, domain [, criteria])

Parameter : This method accepts three-parameter as mentioned above and described below :

  • expr : It identifies the numeric field whose values to be totaled. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field. In expr, the name of a field in a table, a control on a form, a constant, or a function can be included. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.
  • domain : It is A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter.
  • criteria : It identifies a string expression used to restrict the range of data on which the DSum function is performed. It is optional. It is the WHERE clause to apply to the domain.

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.

Table – Product Details :

PRODUCT_ID PRODUCT_PRICE
101 15000
102 10000
103 11000
104 6000

Example-1 : Finding the sum of all product price.

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

Output :

Total_Price
42000

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 :

Total_Price
25000

2. DAvg() Function :
In MS Access, the DAvg() function is used to calculate the average of a set of values in a specified set of records (a domain). The DAvg functions return the average of a set of values from a field that satisfy the criteria. The difference between DAvg and Avg is that in DAvg function, values are averaged before data is grouped and in the case of Avg function, the data is grouped before values in the field expression are averaged.

Syntax :

DAvg (expr, domain [, criteria])

Parameter : This method accepts three-parameter as mentioned above and described below :

  • expr : It identifies the numeric field whose values to be averaged. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field. In expr, the name of a field in a table, a control on a form, a constant, or a function can be included. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.
  • domain : It is A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter.
  • criteria : It identifies a string expression used to restrict the range of data on which the DAvg function is performed. It is optional. It is the WHERE clause to apply to the domain.

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.

Table – Product Details :

PRODUCT_ID PRODUCT_PRICE
101 10000
102 20000
103 30000
104 40000

Example-1 : Finding the average of product price.

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

Output :

Avg_Price
25000

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 :

Avg_Price
15000

Last Updated : 18 Sep, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads