Unit 8. Summary queries (II)

Grouping records

Until now the summary queries that we have seen use all the rows of the table and produce just one resulting row.

The Group by option allows us to define grouping fields. A summary query without grouping fields obtains only one resulting row and the calculations are performed on all the source records.

When a grouping field is included, Access forms groups with all the records that have the same value in the grouping field and every group formed this way generates a row in the result of the query, furtharmore, the defined calculations are carried out on the records of every group. In this way Subtotals can be obtained

E.g if we want to know how many customers we have in each state we need to indicate that we want to count the records of the Customers table but firstly grouping them by the State field. In this way the count() function will calculate on every group of records (clients of the same state). The query will remain like this:

The memo and OLE type fields can not be defined as grouping field.

Rows can be grouped by up to 10 fields, in this case the order of the field names determines the grouping levels from the highest to the lowest level of grouping.

All the rows that have a null value in the grouping column form a single group.

Including expressions

An Expression option allows us to put an expression instead of a field name in the Field: row. This expression has certain limitations.

It can only contain field names with an aggregate function (the functions we just looked at (sum( ), Avg( ), StDev( ), Min( ), Max( )...), fixed values or names of columns that appear with the Group by option.

An expression can combine various aggregate functions, but it is not possible to nest aggregate functions, e.g an expression could be Max(hours)-Min(hours) but not Max(sum(hours)).

Including search criteria.

The Where option allows a search criteria to be placed which is applied on the source rows of the query before performing the calculations.

For the search criteria, the same operators as in a normal query can be used, multiple conditions can also be written (joined by the Or, AND,NOT operators).

E.g we want to know how many customers we have in California, for this we need to count the records from the Customers table but previousely selecting those from California (State="CA"), this is defined in the following way:

We can also include a search criteria in a column that does not have a Where option, in this case the condition will be applied on the resulting rows.

For the selection condition the same operators as in a normal query can be used, multiple conditions can also be written (joined by the Or, AND,NOT operators), a limitation exists in the Criteria: row, the name of a column can not be put down if this column is not a grouping field.

To practice you can perform the Step by step exercise in creating summary queries

Unit 8 exercise.

  Unit 8 evaluation test.

Page. 8.2