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
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.