Creating an Update Query to Update a Salaries and Wages Database

Imagine you have responsibility for the management of a Company's Salaries and Wages database.  The company employs 200 colleagues, and it is your job to process the annual pay increase. The problem you face is that different groups of employees receive a different percentage rate increase; and each employee within a group may have a different salary level to start with.  So what is an efficient way to update each employee's salary without having to having to go through each record manually?  A good solution would be to use an Update Query.

Update Queries are a type of Action Query, similar in nature to Delete Queries, which I blogged about last month.  However, whereas a Delete Query will find a group of records matching a given criteria and delete them from the table, the Update Query will find a group of records matching a given criteria and update one or more fields in each of those records.  So for example, we can use an Update Query to update the salaries of all staff with the job title, Telesales Administrator.

The great thing about using an update query is that we can update a field to an absolute value or a value based on a calculation.  So in addition to being able to update all Telesales Administrator salaries to a fixed value such as, say, £15000, we could also update all existing Telesales Administrator salaries by, say, 5%.  So in a scenario where each Telesales Administrator is on a different salary level to begin with, it makes sense to do the latter to update each unique salary figure.

Please feel free to download the completed solution to the above scenario by clicking this link: Example Update Query.  I recommend you begin by opening tblEmployees and take a look at the 12 sample records and then see how the records for Telesales employees change after the qryUpdateTelesalesSalaries Update Query is run.  You can also follow the instructions below to find out how to create this Update Query yourself.

Figure 1: tblEmployees.  As you can see, there are 6  Telesales Employees.
Notice how their salaries change after the Update Query is run.  

Creating an Update Query

So how do we create this Update Query?  It is easy.  We do it in two stages.  First of all we create a standard Select Query which uses a criteria to find the group of employee records who have Telesales as their job title.  Then we convert the query to an Update Query, and in the process, we enter the calculation which updates the existing salary level for each Telesales Administrator.   Lets do this step by step:

Stage One - Testing the Criteria with a Select Query

1. Click the QUERY DESIGN icon (located on the QUERIES group of the CREATE ribbon).
2. Select the tlbEmployees table from the SHOW TABLES dialog form.
3. Double click the JobTitle and Salary fields from the employees table, so they appear as columns on the Query Design Grid.
4. Enter ="Telesales" in the criteria row of the JobTitle column.
5. Click the RUN icon to run the query.

The results of the query are shown in the screenshot below:

Figure 2: Results of the Select Query
in stage one.

As you can see, the query has found all six records where the employee's job title is Telesales.  We could have included their names and employee id's in the results, but this is not necessary.  Once we convert it to an Update Query and click run, we won't actually see the query results until we open the underlying table and note the updated fields in each of the records matching the criteria.

Stage Two - Converting to an Update Query

1. Re-open the Select Query in Design View.  If the Select Query is already open in DATASHEET VIEW, simply click the DESIGN VIEW icon from the VIEWS group of the HOME ribbon.  Otherwise you can highlight the Query name in the NAVIGATION PANE, right click the mouse, and then click the DESIGN VIEW icon from the drop down menu.
2. Once the Query opens in DESIGN VIEW, click the UPDATE QUERY icon from the QUERY TYPE group of the DESIGN ribbon. The Icon is then highlighted in orange. 
   You will also notice that the SHOW and SORT rows of the QUERY DESIGN GRID have disappeared, being replaced by a new row called UPDATE.
3. All we need to do now is enter the calculation into the UPDATE row of the Salary column.  The calculation to enter is:  [salary]*1.05
   
   

   

   Figure 3: This is what the Query Design Grid should
   now look like for the Update Query.
4. Click the RUN icon from the RESULTS group of the DESIGN ribbon.  
5. Click YES when you  receive a warning message saying "you are about to update 6 row(s)".

Once you have run the query and clicked YES to the warning message, you won't see the changes until you re-open (or refresh) the underlying tblEmployees table.  When you do so, you will see that the Salaries for Telesales staff have been updated by 5% as expected (see figure 4 below).

Figure 4: The Updated tblEmployees Table.