DSum() and DAvg() Functions MS Access

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
My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.