Update Query Updating Records in Microsoft Access and using

Updating Records in Microsoft Access with an Update Query:

 

A state of affairs could arise wherever we'd like to update several records in a very information table once sure info changes or
has to be changed.A Microsoft Access Update question updates given values in a very table for all records or for those records that match a given criteria.

It is attainable to update records manually in a very information table, either via a kind or through the tables datasheet, but this might take a really long-standing. dynamical records manually isn't solely time overwhelming, it's conjointly inefficient, and lends itself to errors as you update the records.

The best thanks to handle this sort of event, the change of the many records in a very information table, is to use associate Update question to create the changes to knowledge in one operation. you'll save yourself time, and eliminate the likelihood of manual errors.

Note: Due to the permanent effects of operative with associate update action question, you want to unceasingly produce a backup copy of your tables, or your data before making an attempt this feature.

How to create a Microsoft Access Update Query:

1.  Create a pick query to see the records that may be updated. Apply any needed query criteria, and examine the information that may be updated by pressing the Datasheet button.
2. In the query style read, click on the drop-down arrow to the correct of the query sort button and select 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.

Using an update query

As a best practice when creating an update query, first create a select query that identifies the records that you want to update, and then convert that query to an update query that you can run to update the records. By selecting data first, you can verify that you’re updating the records you want before you actually change any data.

TIP   Back up your database before you run an update query. You cannot undo the results of an update query, and making a backup helps make sure that you can reverse your changes if you change your mind.

How to back up your database

1. Click the File tab, and then click Share.
2. On the right, under Advanced, click Back Up Database.
3. In the Save Backup As dialog box, specify a name and location for the backup copy, and then click Save.
   Access closes the original file, creates a backup, and then reopens the original file.
   To revert to a backup, close and rename the original file so that the backup copy can use the name of the original version. Assign the name of the original version to the backup copy, and then open the renamed backup copy in Access.

In this section

Step 1: Create a select query to identify the records to update

Step 2: Update the records

Step 1: Create a select query to identify the records to update

1. Open the database that contains the records you want to update.
2. On the Design tab, in the Macros & Code group, click Query Design.
   The query designer opens, and the Show Table dialog box opens.
3. Click the Tables tab.
4. Select the table or tables that contain the records that you want to update, click Add, and then click Close.
   The table or tables appear as one or more windows in the query designer, and the windows list all the fields in each table. This figure shows the query designer with a typical table.
   
   1. Table shown in the query designer
   2. Query design grid
5. Double-click the fields that you want to update in the table windows. The selected fields appear in the Fieldrow in the query design grid.
   You can add one table field per column in the query design grid.
   To add all the fields in a table quickly, double-click the asterisk (*) at the top of the list of table fields in the table window. This figure shows the query design grid with all the fields added.
   
6. To limit the query results based on field values, in the query design grid, in the Criteria row, enter the criteria that you want to use to limit the results.
   Table of example criteria
   The following table shows some example criteria and explains the effect that they have on the results of a query.
   NOTE   Many of the examples in this table use wildcard characters to make the query more flexible or powerful.