These functions are used to retrieve respectively the maximum and minimum values in the specified field. Consider the following example : DMin (“Amount”, “Vouchers”, “Debit = ‘711001’”) Dmax (“Amount”, “Vouchers”, “Debit = ‘711001’") In the above examples, the amount of minimum purchase transaction and maximum purchase transaction is retrieved and reported. It may also be noted that ‘711001’ is the code of Purchase account in Accounts table (c) DSum : This function computes and returns the sum of the values in the specified field or expression. For Example, in a table : Sales that contains ItemCode, Price and Quantity as fields, the total amount of sales may be computed by using the DSum () function as follows : DSum (“Price*Quantity”, “Sales”) However, if the total sales is to computed for a particular item coded as , the DSum () function shall be applied as follows : DSum (“Price*Quantity”, “Sales”, “ItemCode = ”) (d) DFirst and DLast : These functions are used to retrieve respectively the values in the specified field from first and last physical records.
Consider the following application examples : DFirst (“Name”, “Accounts”) DLast (“Name”, “Accounts”) In the above examples, the name first and last account that physically exists in Accounts table is retrieved and reported. (e) DCount : This function is meant to compute the number of records with non-null values in the specified field. Consider the following application example : DCount (“*”, “Accounts”) In the above example, The number of records in accounts table are counted and reported by DCount () function. A- .
SQL Aggregate Functions The SQL aggregate functions have the functionality similar to that of domain aggregate function. However, unlike domain aggregate functions, these functions cannot be called directly into controls used in Forms and Reports of Access. These functions are used in SQL statements that provide the underlying record source of Forms and Reports. All these functions, when used require the GROUP BY clause in SQL statement : (a) Sum : This function is used to compute and return the sum of a set of values.
For Example, consider the following SQL