Summarizing Data with Totals

Queries can summarize and display information by groups. A group is a collection of related records. A sales database could group all orders by each customer. A property database could group all equipment by manufacturer. Our database could group all faculty by department.

Grouping then allows further analysis of the data. For example, we could total, average, or count some value for each group.

Understanding Totals and Groups

We will create a query that will show the total number of faculty members in each department.

Step1. To start to create a new query, on the Ribbon,

Click the Create tab, Click 

We see the Query Design view, with the Add Tables pane on the right side of the screen. To see the total number of faculty in a department, we need to add the Departments and Faculty tables to the query.

Step 2. To view the list of tables, if necessary, at the top of the Add Tables pane,

Click the Tables tab

Step3. To add required tables to the query design grid, in the Show Table dialog box,

Double-Click tblDepartments, Double-Click tblFaculty

We see the Departments and Faculty tables in the query designer. We can now select the necessary fields for the query. Since we want to gather information about each department, we will add the DeptName field to the grid.

Step4. To place the DeptName field in the query, from tblDepartments,

Double-Click DeptName

We will use the EmployeeID field to count faculty for each department since it has a unique value for each faculty member.

Step5. To add the EmployeeID field to the query, from tblFaculty,

Double-Click EmployeeID

We see two fields in the query design grid. We are ready to calculate totals.

Step6. To turn on the Totals function, in the Show/Hide group of the Query Design tab,

Click 

We see a new row labeled Total in the query design grid. Notice the default setting of Group By in each cell of the Total row. We will leave that setting in the DeptName column because we want to group by the DeptName field. We will change the Total row value in the other column, EmployeeID, to indicate a calculation we want Access to make in returning these records.

Using Totals in a Query

Let's look at the ways Totals can summarize our data. Access allows the choice of a number of options and aggregate functions in the Total row. Aggregate calculations are predefined operations performed on groups of records. They can provide totals, counts, averages, and other information about field values in all records or in groups of records. Following is a list of the available aggregate functions:

Using the Count Function

The Count function returns the number of unique items in each group. So in our example, if we group by department and choose the Count function in the EmployeeID column, the query will group all faculty by their department and count them. Let's use the Count function to count the employee IDs in each group.

Step1. To see the various functions,

Click the EmployeeID Total row

We see a drop-down arrow.

Step2. To see the available aggregate functions, in the Employee ID Total row,

Click 

We see the list of functions.

Step3. To select the Count function, on the drop-down list,

Click Count

The query design grid defines the group as Department and the count as EmployeeID.

Step4. To view the query result,

Click 

You see:

NOTE: The departments are in alphabetical order, but not because we sorted them. They are actually in order by department code, which is the structure of the underlying data.

Step5. To see the entire label in the first column,

expand the first column of the Datasheet

Step6. To see the entire label in the second column,

expand the second column of the Datasheet

Notice that the EmployeeID field label has automatically changed to
CountOfEmployeeID to identify the information in the column. We will rename the second field to make it more descriptive.

Step7. To return to Design View,

Click 

Step8. To position your cursor properly, in the query design grid at the bottom of the screen,

Click before the text "EmployeeID"

Step9. To change the label, for the second column, type:

Number of Faculty: Enter key

NOTE: Remember, you have to include the colon as the separator between the caption and field name, but the colon will not be displayed in the field.

Step10. To see the result of changing the label, on the Ribbon,

Click 

The heading for the second column is correct.

Let's save the query. The name we are giving the query will be appropriate when we complete working on it.

Step11. To save the query, on the keyboard, press:

Control key + S, type: qryFacultySalaryAggFunctions Enter key

Now let's make some additional changes to the query.

Using the Avg Function

Another piece of data that would be useful to include is the average of faculty salaries in each department. We can modify qryFacultySalaryAggFunctionsto retrieve this information.

Step1. To return to Design View,

Click 

We will add the Salary field to the query design grid.

Step2. To add the Salary field, from the tblFaculty field list,

scroll down, Double-Click Salary

We see three fields - DeptName, Number of Faculty: EmployeeID, and Salary - in the query design grid. Notice that the Total row default is Group By for the newly-added Salary field.

We want to see the average salary for the faculty in each department. We need to change the Total row setting for Salary from Group By to Avg.

Step3. To select the field to be changed,

Click the Salary field Total row

We see the cursor flashing and the Total options drop-down arrow appears.

Step4. To select the Avg function,

Click , Click Avg

The query is now set up to group the data by department and display the count of employees and their average salary.

Next, we will give the new field a more descriptive name to indicate that a calculation will be displayed.

Step5. To position the cursor properly, in the Salary field,

Click before "Salary"

Step6. To rename the field, type:

Average Salary: Enter key

Sorting the records so that the highest average salary is at the top could be helpful. We will specify a sort order.

Step7. To begin to change the sort order,

Click the Salary Sort row

Step8. To change the sort order to descending, type:

d

The sort order is descending. Let's view these changes to the query.

Step9. To view the query result, on the Ribbon,

Click 

Step10. Expand any columns, if necessary.

We see a datasheet showing each department, the total number of faculty in each department, and the average salary paid to faculty in that department:

Step 11. To save the changes to the query, on the keyboard, press:

Control key + S

The changes are saved.

Understanding More Complex Groups and Totals

A query can be grouped by multiple fields. For example, a query could group three fields to identify an employee: EmployeeID, LastName, and FirstName. In the totals row, all three of these columns would be set to the Group By option.

Next we will add the Rank field to this query in order to view the summary for each rank of faculty.

Step1. To switch to Design View,

Click 

We will add the Rank field to the query design grid between the Department field and the Number of Faculty: EmployeeID field. Double clicking the field name would add it to the end of the grid, but we can also press and drag the field to position it where we want it. Since we want it positioned between DeptName and Number of Faculty: EmployeeID, we will use the press and drag method.

Step2. To add the Rank field to the grid, starting in tblFaculty, in the top panel,

Press & Drag Rank until it is on top of the Number of Faculty: EmployeeID field

The Number of Faculty: EmployeeID field moves to the third column. Notice that the default Total row setting for Rank is Group By. Since we want to see a group for each rank of faculty, we'll keep that setting.

Let's also add an ascending sort to the Departments field.

Step3. To list the departments in alphabetic order, in the DeptName field,

Click the Sort field, type: a

The sort order will be first in ascending order by Departments, then descending order by Total Salary: Salary. Access reads these settings from left to right.

Step4. To switch to Datasheet View,

Click 

Each rank of Faculty is grouped and counted within each department, with the salary summed for each group.

Adding a Parameter

We could expand the usefulness of this query by including a parameter to allow the user to specify a rank. The resulting summary would then show the total salaries for all professors, all assistant professors, or whatever rank was entered. We will add criteria in the Rank column and enter the expression that will prompt the user for a rank of faculty when the query is executed.

Step1. To return to Design View,

Click 

Step2. To set the criteria to make this a parameter query, in the Criteria row under Rank, type:

Like [Enter Rank] Enter key

Step3. To switch to Datasheet View,

Click 

Step4. To supply a rank for the parameter, type:

Prof* Enter key

The query displays the number of employees and their total salaries grouped by Rank and Department:

Step 5. To save the change to the query, press:

Control key + S

Step6. Close the query.

Next, we will create a query that will calculate the students' grade point average (GPA).