Update queries in Access for salary increases

To expand your knowledge in Microsoft Access, we will look at the use of update queries. In this tutorial, you will learn how to efficiently adjust salaries in a table to implement a salary increase, for example. This method is particularly useful when working with numerous records and requires bulk changes.

Key Insights

• With update queries, you can change multiple records at the same time.
• When changing salaries, handling decimal values and formatting is important.
• A backup of the data before an update is essential.
• Salary adjustments can be automated by multiplying with a factor, such as for annual increases.

Step-by-Step Guide to Update Query

At the beginning, we will create a table for the drivers, in case the salaries have not yet been recorded. We will add a new field for the salaries.

First, switch to the design view of your table and add a new field named “Employee Salary”. For the field types, select “Number”, where you can also use decimal values. Set the format to Euro to ensure clarity.

After saving the table, you will see that no salaries have been assigned yet. This can become problematic when many entries need to be updated. To perform a standardized salary increase, you need an update query.

Switch to “Create” and click on “Queries”. Select “New Query Design” and choose the Drivers table for which you want to create the query.

Now switch to the view, and you will notice that the salary is still empty. We now want to create an update query to assign all drivers a salary of 2200 Euros. Click the button to change the query type. Instead of a select query, choose “Update Query”.

New fields will now appear in the query view, including “Update” and “Criteria”.

You can select the field “Employee Salary” and set the value to 2200. You also have the option to specify specific criteria. For example, if you only want to change the salary for all drivers named “Rene”, you can set that condition.

This now looks as follows: You have created the query, but to make the changes, you must first run it. Go back to the query and click “Run”. A dialog box will open informing you that three rows will be updated.

Before proceeding, it is important to create a backup of your data. This is a best practice to avoid data loss. Make sure you have made the backup, as changes to the records cannot be undone. Confirm the process by clicking “Yes”.

Now switch back to the Drivers table, and you should see that all drivers now have a salary of 2200 Euros.

To prepare for future salary increases, the query can be saved. Assume the year is now 2017 and you want to increase salaries by 5%. Instead of adjusting each record manually, you can accomplish this with an update query.

Edit the query by adjusting the Employee Salary with a multiplier of 1.05. This will efficiently raise all salaries by 5%. Save the changes and run the query again.

As soon as you click “Run”, the program will display the number of updated fields. Confirm the update, and you will see that the salaries have now risen by 5%, which saves you significant work.

You can also adjust the conditions. If you only want to increase the salary of certain employees, for example, just the driver “Rene”, you can adjust and run the query accordingly.

This gives you the flexibility to adjust salaries according to your needs, whether for entire groups or specific individuals. This illustrates how versatile and useful update queries are in Access.

The lesson ends here; in the next video, we will focus on append queries.

Summary – Effective Use of Update Queries in Access

By using update queries in Microsoft Access, you can effectively and quickly adjust salaries or other records. Don't forget to regularly create backups of your data to avoid potential losses.