Using an Update query to replace data in Microsoft Access 2019

An Update query cannot be used toadd records or delete records. The query is used to edit, add, or deletedata in a single record or multiple records. The concept of an Update query is much the same as the Find and Replace option, but definitely morepotent. As you cannot undo the effects of an Update query, you should back up thedatabase prior to running the query. The only way to reverse the effectsof an Update query is to run anotherUpdate query or edit the records byhand. To create such a query, follow these steps:

  1. Use the same database as the one shown in the preceding example tocreate an Update query. Create a newquery in Design View.
  2. Choose the table on which you will run the Update query. Click on Add then Close to move to the query grid. For thisexample, we will use FeeAccountstTbl.
  3. Click on the Update icon on theQuery Type group, as illustrated inthe following screenshot:
ms office 258
  1. Notice that the query grid has changed to include the row calledUpdate To:, as illustrated in thefollowing screenshot:
ms office 193
  1. Add only the fields that require updating and the fields you wouldrequire for criteria purposes from the table onto the grid. Double-clickto add the field. For this example, we will update the YEARLY FEE TOTAL data located in the FeeAccountsTbl table by 10%.
  2. Make sure that the Field: rowcontains the YEARLY FEETOTAL field.
  3. In the Update To: row, enter thefollowing: [YEARLY FEE TOTAL]*1.1, asillustrated in the following screenshot:
ms office 589
  1. Click the Run icon on the Results group. The [YEARLY FEE TOTAL] underlying table fieldconsists of the value 12000.00 atpresent. We are updating this amount by 10%.
  2. You will be prompted with an information box, indicating how manyrows will be updated. Click on Yes tocontinue.
  3. Double-click to view the FeeAccountsTbl table in Datasheet View. The YEARLY FEE TOTAL field is updated to includea 10% increase from 13200.00 to 14520. Note that if the Update query is saved and is run again, itwill affect the underlying table once again. 

Now that you have seen the possibilities of Update queries, we will learn how to add newrecords to a table by copying and pasting records using Append.

Leave a Comment