DVar() and DVarP() Functions MS Access

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