Statistical Functions

Introduction

Besides the Val() and the Eval() function that we saw earlier, Microsoft Access is equipped with functions used to perform routine arithmetic and algebraic operations. It also provides functions used in collections of values. Some functions can "scan" a series of records of a table or a form and provide the appropriate value.

In arithmetic, we are used to calculating the sum of a series of values. In a database, you can also calculate the sum of the values that a certain column holds. This operation can be carried by the Sum() function. Its syntax is:

Sum(FieldName)

This function takes one argument. The argument can be the name of a column. If it is, all the values held by that column, throughout the records of the table, would be summed up and this function would return the result.

The argument to this function can also be an expression. It can even be a combination of columns.

Besides the Sum() function, another arithmetic-oriented function you can use is Count() and it uses the same syntax as Sum(). The Count() function is used to count the number of entries of a column among the records of a table. Here is an example of using it:

=Count([InvoiceID])

In this case, this would return the number of records of the table.

The Avg() function is used to calculate the average value of the values of a column. It uses the same syntax as the Sum() function.

The Min() (or the Max()) function is used to calculate the minimum (or the maximum) of the values of a column. It uses the same syntax as the Sum() function.

The First() (or the Last()) function is used to find the first (or the last) value of a column among the records of a table.

Practical Learning: Using Statistical Functions

1. In the Database window, click Forms. Right-click Invoices and click Save As...
2. Enter InvoicesSummaries as the name of the form and click OK
3. Right-click InvoicesSummaries and click Design View
4. Change the design of the form as follows:
    

   Control Caption Name Control Source Option Group Summary of Invoices     Text Box Nbr of Invoices txtNumberOfInvoices =Count([InvoiceID]) Text Box Labor Expenses txtLaborExpenses =Sum([TotalLabor]) Text Box Average txtAverageLabor =Avg([TotalLabor]) Text Box Parts Expenses txtPartsExpenses =Sum([TotalItems]) Text Box Average txtAverageExpenses =Avg([TotalItems])

5. Preview the form
    
   
6. Save and close the form