How to create an update query in Microsoft Access like a pro? Step by step guide

Update data Manually in a company can be a chore, and it is not without errors. However, when you use the Microsoft Access to manage your database, can create an update query to renew registrations in bulk like a pro. If you want to know how it works, keep reading.

The data update it is a regular process in every company. It involves modifying information; basically, deleting a piece of data that is obsolete to replace it with a new one.

With the help of an update query, you can modify any number of records in a simple operation. Therefore, we will help you learn how to carry out this procedure.

What is an update query in Microsoft Access and what is it for?

Office automation tools Microsoft Windows They offer numerous functions to facilitate administrative tasks. Some of them are complex, such as the macro recording, and others are simpler; like a update query. However, you must be very clear about what it is an update query in Microsoft Access and what it is for exactly.

In basic terms, the update query is a process by which one or more data tables can be updated that are related. Its operation is similar to that of other queries. The user can filter, select or group data until you get the information you want to modify. By means of an update query you have the possibility to edit, add and replace the information of an existing record. However, you can't create or delete records.

For this, it is necessary use an append query and a delete query respectively. Update queries can be thought of as advanced forms, very dialog box style "Search for and replace". But you should know that there are certain differences that are key. Both functions give the user the possibility to select a specific value to replace it with a new one.

Apart from that, it is important to highlight the functions that only update queries have:

You can use criteria that do not depend exclusively on the value you are going to replace.
Allows you to update multiple records in a single operation.
In the same way, you can change the registers multiple tables at the same time.

The platform allows you to update values such as text fields, dates, numeric fields, among others. However, the update queries meet certain restrictions.

For example, you cannot update any of the following fields:

Calculated fields: They cannot be updated since they do not reside permanently in the data tables.
Reference tables query fields or totals query fields: They cannot be updated by means of an update query since these values are calculated.
Automatic numbering fields: These are values that can only be changed by entering a record in the data table.
Single record queries and single value query fields: They represent summary values. It can be a single record, or it can be multiple records. However, it cannot be updated with an update query. It also cannot be achieved manually.
Fields in a union query: Access cannot update the required records because each record that appears one or more times in data sources appears only once in the result of the union query.
Main keys: Those fields that are primary keys cannot be updated unless a relationship is established that allows them to be updated in a cascade, that is, automatically.

Learn step by step how to create an update query in Access

The update queries they greatly simplify the administrative task of manually updating database records. But you should take into account some recommendations about this function. For this reason, we teach you the step by step how to create an update query in Microsoft Access. First of all, it is necessary to highlight that the update query function is extremely sensitive to errors, which can lead to the destruction of important data.

This is due to:

Changes made by the update above the data table are permanent.
If no backup is made, performing an update query can cause irreparable data loss.
To avoid data loss, it is convenient to represent the result of the update query in a calculated field.
To be able to use the cascade update, you need to change the relationships of the data tables.

Creating a backup is the first step to create an update query. Then you must create a select query to identify the records to be updated. Next, you must convert that query to an update query, which can be executed on the registry values.

The steps to follow are:

Create backup

Open the Microsoft Access app. If you use a version older than Access 2010, go to the tab "Records".
Then click on "Save as". If you have version 2010 or later, display the tab "Records" and click "Save and publish."
On the right side of the menu, click "Advanced". Then select "Database Backup".
Then a dialog window opens "Save backup as". You must enter a name to identify the copy, as well as specify a storage location on the computer. Then click "Save".
To create the backup, Microsoft Access close the original document. Then the same application reopens the document.

Create a select query

Open the app of Microsoft Access. Then also open the database that contains the records to modify.
Go to the tab "Create", which is located in the group "Queries". There, click "Query design". You can see how the query designer appears on the screen, along with the dialog box "Show table".
Select the tab "Boards". Next, you must choose the table or tables that contain the records to modify. Then, you must click on "Add". When you're done, tap "To close".
In the query designer, the tables are represented as one or more windows. These windows allow you to view all the fields belonging to each table.
In the table windows, you must double click on each field you want to update. The chosen fields are displayed in the row "Countryside", which is located on the grid of "Query design".
Through the platform Access, in the query design grid, you can add one table field per column.
Similarly, with a double click on the asterisk (*), which is located at the top of the list of fields in the data table window, you can quickly add all fields that are necessary.
In the row of "Criteria", From the query design grid, you have the option of limiting the query results, according to field values. You must specify what criteria you want to use to carry out the limitation.
Next, in the tab "Design", located in the group "Results", you must click "Run".
Check that the select query returns the records that need to be updated.
Select the fields that should not be included in the query design and then press the key "Supr (Suppress)" to remove them.
On the other hand, if you want to add other fields, drag them onto the query design grid.

Change select query to update query

Enter the tab "Design", which is located in the group "Type of query". Then click "To update".
The platform Microsoft Access takes care of adding the row of "Update to" to the query design grid. This is how you can change a select query to an update query.
Locate the field that contains the value that must be modified. Then enter the expression (criteria for change) in line "Update to" belonging to said field. Only valid expressions are allowed in the row "Update to".
In the tab "Design", of the group "Results", you must click "Run". An alert message appears on the screen.
Run the update query to update the values. Click on "Yes".

By default setting of Microsoft Access, all action queries, such as update or delete, are disabled. To enable the query, just click "Enable content" in the message bar. In the event that the database is located in a location that is trusted, or is signed, the platform will not disable the queries.

What are all the types of queries that can be created in Access?

Queries are a function of Microsoft Access that you gives the user the opportunity to more easily view the information stored in a database. Also, you can still add, modify and delete data.

It is worth noting which are the types of queries that can be created in Access, since each one has its own purpose:

Selection query: It is a process that helps you retrieve the information that is of interest to you. You can select specific data, from one or more sources, such as tables or other queries.
Action query: They are the processes that allow the user to add, modify or delete data. Each task requires a specific type of action query.
Parameter query: The parameter query allows the user to initiate frequent variations of a specific query. Similarly, you can specify the types of data that the parameter can accept.
Totals query: It is considered a select query that gives the user the ability to group and summarize data. In addition, you can make use of the "Sum" function.
Crosstab query: It works to calculate and reshape data so that it is much easier to understand. The data are grouped under two information criteria.
Table creation query: In short, the make table query works to create a new table, based on the data contained in other data tables.
Consultation of attached data: It is a process used to retrieve information from one or more data tables. Furthermore, it allows the user to add such information to other tables.
Deletion query: As its name implies, it is used to remove information from data tables. You can check all the rows to determine the values that should be removed.