“Totals Query”

As it is 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 create a “Staff” table 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”, of course, we can calculate the sum, as well as the average, the maximum and the minimum value.
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 spend on each subject and how many teachers.

 

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

This opens the “Summary Options” dialog.

In this dialog we have a number of possibilities.
We can calculate the total value of the sum, average, minimum value and maximum value.
Because I want the total of each box, I select “Sum”.
At the bottom of this dialog we have the “Count Records in …” option.
When this option is checked, the sum is based on the number of rows.
I check the box and click the OK button.

Then I click “Next”.

In the next window I select “Summary” and click the “Summary Options ” button.
And click the “Finish” button.
The “Query” opens and displays that among other things, the school spends more than 4 million on 3 teachers for “English”.

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

Featured Courses

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