Microsoft Access 2003 free tutorial. Unit 8 Access Summary queries

Unit 8. Summary queries (I)

Definition

In Access we can define a special query to calculate totals on the records of a table (or various tables related). In order to obtain those totals we use summary functions so it is why we call them summary queries.

It is important to remember that the resulting rows of a summary query have a distinct nature to the rest of the rows resulting from queries as each row corresponds to various rows in the source table.

A 'non summary' query row corresponds to a row in the source table and contains data found in just one row of the source, whilst a row from a summary query corresponds to the summary of various rows from the source table, this difference is the origin of a series of restrictions that the summary queries suffer as we will see later. For example, this type of query does not permit us to edit the data.

In the next image we have an example of a normal query in which one visualizes the rows of a table of offices sorted by area, in this case every row of the result corresponds to just one row in the offices table, while the second query is a summary query, every row of the result corresponds to one or various rows in the offices table.

We create a summary query by selecting the Totals option in the View menu ,

or by clicking on the button on the toolbar.

In either case a row is added to the QBE grid, the Total: row.

All the columns that we include in the grid should have a value in this row, this value indicates to Access what to do with the values contained in the field written in the Field: row.

The values that we can indicate in the Total: row are those that appear in the drop down list associated with this cell as we see in the image to the right.

The summary functions

The summary functions are functions that allow us to obtain a result based on the values contained in one column of a table and they can only be used in a summary query.

To use these functions we can write them directly in the Field: row of the grid as we will see ahead, but we can also use a simpler method which is selecting from Total: row in the grid the option corresponding the function.

Next we will describe these options.

The Sum function calculates the sum of the values indicated in this field. The data being added up need to be numeric type (integer, decimal, or currency...). The result will be the same type though might have a higher accuracy.

The Avg function calculates the average of the values contained in the field being summarized. It also applies to numeric data, and in this case the type of data in the result can change depending on the systems necessities in represent the value of the result.

The StDev function calculates the Standard deviation of the values contained in the column, assuming the values are a sample of a larger population. If the query source has less than two records, the result is null.

The Var function calculates the variance of the values contained in the column. If the query source has less than two records, the result is null.

It is interesting to distinguish that the null value is not the equivalent of 0, the summary functions do not consider null values while they consider the 0 value as a value, therefore in the average and the standard deviation the results will not be the same with 0 values as with null values.

The Min and Max functions determine the smallest and the largest value of the column. The values in the column can be numeric, text or date. the result of the function will have the same type of data as the column. If the column is numeric, Min will return the smallest value contained in the column, if the column is text type, Min will return the first value in alphabetical order, if the column is date type, Min will return the oldest date and Max the latest.

The First and Last functions are used to obtain the first and last record of the group that is being calculated. Sorting the records does not have any effect on the result of these functions, Access will allways consider the cronological order in the records were created.

The Count function counts the number of values in a column, the data can be of any kind, and the function always returns a integer number. If the column contains null values these values are not counted, if a value is repeated in a column, it is counted various times.

In order for the number of records to be counted the Count(*) function needs to be used, it returns the number of rows and therefore also counts the null values. In this case we need to select the Expression option and write it like this:

To continue with this unit go to the next page...

Page. 8.1

Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.