DMin and DMax Functions MS Access

1.DMin() Function :

DMin() Function in MS Access is used to determine the minimum values in a specified set of records (a domain). The DMin functions return the minimum values that satisfy the criteria. If expression identifies numeric data, the DMin functions return numeric values. If expression identifies string data, they return the string that is first alphabetically. The difference between DMin and Min is that in DMin function, values are evaluated before the data is grouped and in the case of Min function, the data is grouped before values in the field expression are evaluated.

Syntax : DMin ( 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 minimum value. 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 DMin function is performed. It is optional. It is the WHERE clause to apply to the domain.

Returns : 

It returns minimum values in a specified set of records.

Table -ProcuctDetails.

Example-1 : Finding the minimum product price

Select DMin("Product_Price", "ProcuctDetails") as Min_Price;

Output :

Example-2 : Finding the minimum product price for a given condition where the product id is 104.

Select DMin("Product_Price", "ProcuctDetails","Product_Id  = 104") as Min_Price;

Output :

2.DMax() Function -

DMax() Function in MS Access is used to determine the maximum values in a specified set of records (a domain). The DMax functions return the maximum values that satisfy the criteria. If expr identifies numeric data, the DMax functions return numeric values. If expr identifies string data, they return the string that is last alphabetically. The difference between DMax and Max is that in DMax function, values are evaluated before the data is grouped and in the case of Max function, the data is grouped before values in the field expression are evaluated.

Syntax -

DMax( 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 maximum value. 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 DMax function is performed. It is optional. It is the WHERE clause to apply to the domain.

Returns :

It returns maximum values in a specified set of records.

Table -ProcuctDetails.

Example-1 : Finding the maximum product price.

Select DMax("Product_Price", "ProcuctDetails") as Max_Price;

Output :

Example-2 : Finding the maximum product price for a given condition where the product id is 103.

Select DMax("Product_Price", "ProcuctDetails","Product_Id  = 103") as Max_Price;

Output :

jana_sayantan

Follow

Improve

Article Tags :

• SQL
• DBMS-SQL