6. MICROSOFT ACCESS QUERIES – ADVANCED
A Totals query allows you to summarize information in the database. When you summarize data from one or more tables then either:
- you are producing summary data for the whole table, or
- you are producing summary data for specific groups.
For instance, you may want to know
- how many courses there are
- the average of the credit hours
- the number of courses in each department
To create a Totals query you begin by creating a simple query that retrieves all the attributes that will be needed in the summarization, and then click the Totals icon found in the upper-right hand corner of the MS Access window:
When you click the Totals icon the Grid changes to include a Total line. For each field in the grid you must choose from the drop-down:
For each field in the grid you choose one of:
- “Group By”: if the field is used for grouping
- An aggregate function: if the field is to be summarized using that function. We will consider the standard set including sum, average, minimum, maximum, count.
- “Where”: if the field has criteria to be used for selecting rows. Only rows satisfying the criteria contribute to the grouping and display of results.
The simplest type of totalling query displays an aggregate over an entire set of rows. For example, to sum the credit hours over all courses in the University database one can use:
This query summarizes the entire table. The result of this query is one line displaying a sum.
Typically, the use of the Totalling feature is more complicated. Consider the University database and that we need to obtain a count of the number of courses offered by each department. We begin with a query that lists the department code and any other field of the Course table (courseNo is a good choice because it can never be null – nulls are passed over when the counting of field values is performed). The query below lists the fields we need:
In the upper right-hand corner of Design View for queries you must click the Totals icon. When you click the Totals icon a new line (Total line) is added to the grid:
By default, MS Access sets each field up for grouping. To count the number of courses in each department you must click in the Total area for courseNo and change from Group By to Count:
Now you have a query that will show the value of each department code along with a count of the number of courses for the department. This query produces one row per department.
Note the choices for aggregate functions – the first 5 are part of the SQL standard: SUM, AVG, MIN, MAX, COUNT and perform a sum, average, minimum, maximum or count over the values found within a group. When a Totalling query is executed, the following actions are performed by MS Access:
- Rows are retrieved from the underlying table(s): Recall that when Where is specified in the Total line, then there is a criteria that must evaluate to true for a row to be part of this result.
- The retrieved rows are organized into groups where the rows forming a group have the same value for the grouping field(s).
- For each group aggregates are evaluated.
- A group can be eliminated from the results: If in the same column of the grid where Group by or an aggregate function is specified, there is some criteria given in the criteria line, then if the criteria evaluates to false, the pertinent group is excluded.
Write queries for:
1) Consider the last example where the number of courses per department is listed. The sample database is small and so many departments have just 1 course. Modify the query to list results only for departments where there is more than 1 course. For this you must include a criteria >1 for the field where COUNT is specified:
2) Consider the University database:
- For each department list the department code and the largest value for credit hours.
- For each department list the department code, department name, and the number of courses.
3) Consider the Library database.
- List the number of books that have SQL in the title.
- List the number of members by gender.
- What is the total for fines?
4) Consider the Company database
- List the number of employees in each department.
- List departments that have more than 25 employees.
- For each employee who is a supervisor, list the supervisor name and the number of employees they supervise.
- Suppose the Employee table has a salary field holding an employee’s salary. What is the average salary?