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 |