databasedev.co.uk - database solutions and downloads for microsoft access

Updating Records in Microsoft Access with an Update Query:

A situation may arise where we need to update many records in a database table when certain information changes or needs to be modified.

A Microsoft Access Update Query updates specified values in a table for all records or for those records that match a specified criteria.

It is possible to update records manually in a database table, either via a form or through the tables datasheet, however this may take a very long time. Changing records manually is not only time consuming, it is also inefficient, and lends itself to errors as you update the records.

The best way to handle this type of event, the updating of many records in a database table, is to use an Update Query to make the changes to data in one operation. You will save yourself time, and eliminate the possibility of manual errors.

Note: Due to the permanent effects of working with an update action query, you should always make a backup copy of your tables, or your database before attempting this option.

How to create a Microsoft Access Update Query:

1. Create a SELECT query to determine the records that will be updated. Apply any required query criteria, and view the data that will be updated by pressing the Datasheet button.
2. In the query design view, click on the drop-down arrow to the right of the Query Type button and choose Update Query.
3. After you are satisfied that the information to be updated is correct Run the query using the Run Icon

Now consider the following scenario example, that will illustrate the use of a Microsoft Access Update query:

One of your product suppliers has decided to increase the price of their products by 3 percent (%) across the board. You have decided that you will use an Update Query to change all of the relevant records in your Products table.

Our original table appears like the following example:

The original Products table before we run the Update Query

We need to update the Unit Price field, to update all records from our supplier (Exotic Liquids) as they have informed us of a 3% price increase.

Creating the Update Query

Here are the steps that we follow to produce the required outcome:

1. Create a new query using the Products table and the Suppliers table. Include the fields that you are going to use to update the data (ProductID, ProductName and UnitPrice from the Products table, and CompanyName from the Suppliers table)
   
   We have also included criteria in the CompanyName field to limit the results to only those of the Supplier that we are updating the records for.
   
   Also, in the image below, you will see that we have included an additional field, just to test our expression. This will give us a value for the 3% increase, just to check that the results will be returned correctly. This field will be removed before we run the update, but we will use the expression later.
   
   
   Creating a SELECT query, that will later be changed to the UPDATE Query
2. To check what results this will produce, run the query by clicking on the datasheet button
   
   
   Checking the results before running the Update Query
3. When we are happy with the resulting data, we can switch back to design view to convert the query to an Update query.
   
   From the Query Type button on the toolbar, select Update Query
   
   In the curUnitPrice column, in the Update To cell, type in the expression [curUnitPrice]*1.03 and press enter. This expression will update the original Unit Price by 3 Percent.
   
   The query design should now look like:
   
   
   The Update Query design
4. We now need to Run this query, using the Run button to update the data in our Products table that meets the criteria that is applied. The warning dialog box indicates the number of records that will be update, click Yes to accept this:
   
   

Remember that the update query will permanently update records from the specified table(s), therefore it is very important that you have backed up the table(s) or database before running this object.

Check Your Update Query Results

Once you have ran the update query, you can check the results by once again changing the update query back to a select query.