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
Step2. To add required tables to the query design grid, in the Show Table dialog box,
Step3. To close the Show Table dialog box,
Step4. To place the DeptName field in the query, from tblDepartments,
Step5. To add the EmployeeID field to the query, from tblFaculty,
Step6. To turn on the Totals function, in the Show/Hide group of the Ribbon's Design tab,
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:
|Sum||Totals the values for each group|
|Avg||Averages the values for each group|
|Min||Returns the lowest value in each group|
|Max||Returns the highest value in each group|
|Count||Returns the number of items in each group|
|StDev||Returns the standard deviation for each group|
|Var||Returns the variance for each group|
|First||Returns the first value in the group|
|Last||Returns the last value in the group|
|Expression||Returns a calculation based on an aggregate function|
|Where||Specifies criteria to select specific records|
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
Step2. To see the available aggregate functions, in the Employee ID Total row,
Step3. To select the Count function, on the drop-down list,
Step4. To view the query result,
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
Step7. To return to Design View,
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
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,
Step11. To save the query, press:
type: qryFacultySalaryAggFunctions Enter
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,
Step2. To add the Salary field, from the tblFaculty field list,
scroll down, Double-Click Salary
Step3. To select the field to be changed,
Click the Salary field Total row
Step4. To select the Avg function,
Click, Click Avg
Step5. To position the cursor properly, in the Salary field,
Click before "Salary"
Step6. To rename the field, type:
Average Salary: Enter
Step7. To begin to change the sort order,
Click the Salary Sort row
Step8. To change the sort order to descending, type:
Step9. To view the query result, on the Ribbon,
Step10. Expand any columns, if necessary.
11. To save the changes to the query, press:
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,
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
Step3. To list the departments in alphabetic order, in the DeptName field,
Click the Sort field, Type: a
Step4. To switch to Datasheet View,
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,
Step2. To set the criteria to make this a parameter query, in the Criteria row under Rank, type:
Like [Enter Rank] Enter
Step3. To switch to Datasheet View,
Step4. To supply a rank for the parameter, type:
5. To save the change to the query, press:
Step6. Close the query.