Totals Query

As mentioned in the previous lesson, you have a “Query” with totals if there is at least one field of numeric “Data type”.

For this example I make a table “Staff” with the following fields:
“Id”, ” Last Name”, “First Name”, “Subject” and “Salary”.
The “Salary” field is numeric and the notation is “Euro”.
For the rest, there is nothing special.

With the “Totals Query”, we can calculate the sum, as well as the average, the maximum and the minimum values. Select the “Create” tab in the “Ribbon” and click the “Query Wizard”.

In the “New Query” select “Simple Query” and click the OK button.

In the second window I select the “Staff” table and move the “Subject” and “Salary” fields to the “Selected Fields” section because I want to know how much money I spent on each subject and how many teachers are there.

In the next window I select “Summary” and I click the “Summary Options” button.

This opens the “Summary Options” dialog.

In this dialog we have a number of options. We can calculate the total value of the sum, average, minimum value and maximum value. I want the total of each box, so I select “Sum”.

At the bottom of this dialog we have the “Count Records in …” option. When this is checked, the sum is based on the number of rows. I check the box and click the OK button

Then click “Next”.
In the next window of the Wizard, I give the “Query” an appropriate name.

Click the “Finish” button.
The “Query” opens and displays that among other things, the school spends more than 2 million for 2 teachers for “English”.

In the next lesson let’s see how we make a “Query” in “Design View”.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats