1. DVar() Function :
DVar() Function in MS Access is used to estimate variance across a set of values in a specified set of records (a domain). DVar function evaluates variance across a population sample.
Syntax :
DVar (expr, domain, criteria)
Parameters :
This method accepts three-parameter as mentioned above and described below :
- expr : It identifies the field for which we want to find the variance. 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.
- domain : It identifies 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 DVar function is performed. It is optional. It is the WHERE clause to apply to the domain.
Returns : If domain refers to fewer than two records or if fewer than two records satisfy criteria, the DVar functions return a Null, indicating that a variance can’t be calculated. Otherwise, it returns the variance across a population sample.
Table – ProductSales :
| Product_Id | Product_Price |
|---|---|
| 101 | 10000 |
| 102 | 11000 |
| 103 | 12000 |
| 104 | 13000 |
Example-1 : Variance of the product price.
SELECT DVar("Product_Price", "ProductSales") AS DVar_Value ;
Output :
| DVar_Value |
|---|
| 1666666.6666667 |
Example-2 : Variance of the product price whose price is less than 12500.
SELECT DVar("Product_Price", "ProductSales", "Product_Price<12500") AS DVar_Value ;
Output :
| DVar_Value |
|---|
| 1000000 |
2. DVarP() Function :
DVarP() Function in MS Access is used to estimate variance across a set of values in a specified set of records (a domain). DVarP function evaluates variance across a population.
The main Difference between Dvar and DvarP is that DVarP function evaluate variance across a population and the DVar function evaluates variance across a population sample.
Syntax :
DVarP (expr, domain, criteria)
Parameters :
This method accepts three-parameter as mentioned above and described below :
- expr : It identifies the field for which we want to find the variance. 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.
- domain : It identifies 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 DVarP function is performed. It is optional. It is the WHERE clause to apply to the domain.
Returns : If domain refers to fewer than two records or if fewer than two records satisfy criteria, the DVarP functions return a Null, indicating that a variance can’t be calculated. Otherwise, it returns the variance across a population.
Notes : In DVar or DVarP function, values are calculated before data is grouped and in Var or VarP function, the data is grouped before values in the field expression are evaluated.
Table – ExamDetails :
| Sub_Id | Marks |
|---|---|
| 1 | 88 |
| 2 | 95 |
| 3 | 96 |
| 4 | 100 |
Example-1 : Variance of Marks.
SELECT DVarP("Marks", "ExamDetails ") AS DVarP_Value ;
Output :
| DVarP_Value |
|---|
| 18.6875 |
Example-2 : Variance of the marks which are >90.
SELECT DVarP("Marks", "ExamDetails ", "Marks>90") AS DVarP_Value ;
Output :
| DVarP_Value |
|---|
| 4.6666666666667 |