Awards
Join OfficeTipsAndMethods

Have some suggestions to share with the world?

Join OfficeTipsAndMethods and share your insight in a Comment. You must be a member to comment and all comments are moderated before being published. Membership is free and we would never share your personal information with anyone.

Become an OTM Member

Log In

YouTube Video Channel
OfficeFix
Article Categories

Summary Queries

Download the demonstration database for this article from

In the previous article in this series, we looked at creating calculated query fields. While they not strictly speaking doing calculations, totals queries provide the means to summarize data, in which data can be grouped and numeric fields totalled or otherwise summarized.

For example, in the example database for this article, there is a table of employee salary information. The table stores current and past salaries for each employee along with the date that the person’s salary became effective. A query can be used to produce a list showing each person’s current salary along with the date he or she started receiving payment at that rate.

image

The graphic shows a query open in design view. This is the Access 2010 designer. In Access 2003 and earlier look on the query design toolbar for the totals (S) button. When you click it, the Total row will open in the query grid. When you click a cell in the Total row, a dropdown list opens with the available choices.

image

Each time a person’s salary changes, a new record is created in the salaries table. As you can see in the field list, the fields in this table are EmployeeID, to identify the related record in the employees table, Salary, the new salary, and SalaryEffectiveDate, the date on which the salary becomes effective.

As time goes by each employee may have several records in this table, one record for each change in pay. The grouping query allows us to group all salary records for each employee together in the datasheet. If data within the group is sorted into descending order by effective date then the first record in each employee group will be latest effective date and the corresponding salary will be the employees current salary. Keep in mind that if future salary changes have been recorded, you will need to use date criteria to ensure that those future salaries are excluded.

 

Here is the result of the query in datasheet view.:

 

image

This query can now be used as the data source for a report or form to inform management of each person’s current salary level and for how long they have been receiving that salary.

Now, here is a little more involved example. It has been decided to increase every employee’s salary in 2011 by 3% effective the first of the month in which their employment anniversary falls.

Here is the query design to do the calculations.

image

And the resulting datasheet:

image

Notice that we had to include the employees table because the query needs to know the employee’s data of hire to determine the anniversary month.

This query is not quite in its final form. We have included the literal value for the year, 2011. That would mean the query could only be used for 2011 salary calculation.

In the next article we will make the query more flexible by making it a parameter query.

Comments are closed.