Create a request to update data. Create and execute an update request

To update data in table fields, an update query can be used. Changes are made to a group of records that are selected using user-specified selection criteria. Values ​​for field changes are defined in the request form in the Update field.

For example, by mistake, a Sirius Media client who is actually an InvestCapitalBank client was listed as a VTB 24 client. To update the data, select Create> Constructor> Tables> Clients (Fig. 37).

Figure 37 - Creating an Update Request in design mode

From the taskbar, select Request> Request Type> Update Request. We have a new line Update (Fig. 38).

Figure 38 - Selecting the type of request

Fill out the request by selecting Field - Name, Table name - Client, Update - InvestCapitalBank, Selection condition - VTB 24 and indicate the client - Sirius Media. Save the changes and rename Request 1 to Update Request (Fig. 39).

Figure 39 - Saving and renaming the Update Request

Figure 40 - Confirmation of the update request

Request to update records
A record update query makes general changes to a group of records one name for multiple tables. The data changes in the base tables.

Create a query to update table records.
1. Create a new query to include tables containing records to be updated.
2. In the Design mode, click the Query type button on the toolbar and select Update.
3. Move with the mouse from the list of table fields the fields that need to be updated or define a selection condition for them.
4. For the fields to be updated, in the Update cell, enter the expression or value that should be used to modify the fields.
5. For the fields by which you want to filter the updated records, enter the selection conditions.
6. To update the records, click the Run button on the toolbar.

1. Museum
2. Art Gallery
3. Video rental
4. Garment factory

Key words: database access; obd access; subd access; access databases; access example; programming access; ready database; creating a database; DBMS database; access coursework; database example; access program; access description; access abstract; access requests; access examples; download database access; access objects; db in access; download subd access; ms access database; subd access abstract; subd ms access; the benefits of access; database; download the database to access; Database; relational database; database management systems; exchange rate database; download the database; access database download; access databases download; abstract database; database examples; term papers on DBMS; normalization; database; database structure; example database; base of requests; training database; database design; data; description of the database; subd abstract; create a database; database on; use of the database; term paper database; ready; using a DBMS; DB table; database 2003 download; base.

Update requests are used primarily to update a large number of records at once with a single request. A classic example of such a query is the price change in the table " Products"(Products) by some constant coefficient, for example, you can increase the price of one of the product categories by 20%. To create such a request:

1. Expand the list of tables by clicking on the shortcut Tables(Tables) database windows.
2. Select the table " Products"(Products). Left-click on the arrow on the button New object(New Object) on the toolbar and select a value from the list Inquiry(Query). The Query Builder window appears with the Products table at the top. Transfer the "Price" and "TypeCode" fields to the request form.
3. Enter a criterion for selecting records: for example, in the "TypeID" column, enter the value 1.
4. Run a query to ensure that all records containing drinks are selected.
5. Now let's change the request to be an update request. To do this, run the menu command Request ›Update(Query ›Update). The request header changes and an additional line appears Update(Update To). At the same time, the lines Sorting(Sort) and Output on display(Show) (fig. 8.8).
6. Now you need to line Update(Update To) enter an expression by which the new price will be calculated: [Price] * 0.2. In other cases, you can enter a constant, for example, if you need to change the date in many records to the current one.
7. Now you can run your query. To do this, press the button Running(Run) on the toolbar. As with adding records to a table, Access displays a message about the number of records to update and asks for confirmation to update. You can confirm the update of the records or reject it.

Rice. 8.8... Request to update records

Advice
Before executing an update request, left-click the arrow on the button View(View) and select Table mode(Datasheet View). You will see all the records that will be updated, although the data in the updated fields will still be old. And only after making sure that the necessary records are selected, you can execute the query.

In the given example, the values ​​changed in a field that is not the primary key in the table " Products"(Products). A special case occurs when you want to update the primary key value in a table. If this table has a one-to-many relationship with other tables, then changing the primary key of a record must simultaneously change the foreign key values ​​in all related records. Access makes this change automatically because it supports cascading updating of records. When defining a relationship between tables, you can select Cascading related fields update(Cascade Update Related Fields) (see the section "Maintaining data integrity", Chapter 2).

If this check box is selected, then when the value of the primary key in the master table changes, Access automatically runs a special query that updates the foreign keys of all related records in the subordinate table.

By using an update query in Access databases, you can add, change, and delete data in existing records. Refresh requests can be thought of as a kind of dialog box Find and replace with more options. An update query cannot be used to add new records to or remove from the database.

To add new records, use the add request, to delete records, use the delete request.

In this article

Overview

Below are the similarities and differences between Find and Replace and Update Request

As in the dialog box Find and replace, in the update request, you can specify the value you want to replace, as well as the new value.

Unlike the function Find and replace an update request allows you to:

use conditions that do not depend on the value being replaced;

update multiple records at a time;

modify records in multiple tables at the same time.

Restrictions on updating fields

An update request cannot be used to update data in the following types of fields.

Calculated fields. Calculated field values ​​are not permanently stored in the table. Once calculated, they exist only in the temporary memory of the computer. Because calculated fields do not have a permanent storage location, they cannot be updated.

Summary or cross-reference fields. The values ​​in these types of queries are calculated, so they cannot be updated using an update query.

Fields with the "Counter" data type. The values ​​in the fields with the "Counter" data type change only when a record is added to the table.

Fields in Unique Value Queries and Unique Record Search Queries The values ​​in such queries are summarized. Some values ​​represent one record, while others represent more than one record. The update operation is not possible because it is impossible to determine which records have been excluded as duplicates, and therefore it is not possible to update all the required records. This limitation applies if you are using an update query or trying to manually update data by entering values ​​into a form or table.

Fields in a union request You cannot update data from fields in a join query because each record that appears across multiple data sources appears only once in the results of a join query. Because some duplicate records are removed from the results, Access cannot update all of the required records.

Fields that are primary keys. In some cases, such as when a primary key field is used in a table relationship, it is not possible to update the field using a query, unless the relationship has been configured to automatically cascade updates.

Note: In a cascading update, Access automatically updates the foreign key values ​​when the primary key value in the parent table changes.

Using an update request

To create an update request, we recommend that you do the following: create a select query to identify the records that you want to update, and then convert it to an update request and update the records. This sequence makes sure that you are updating the correct records.

Advice: It is recommended that you back up your database before running an update request. The results of this query cannot be reversed, and the created backup will allow you to restore the data if necessary.

Creating a database backup

Open the tab File and select the command Save as... Please note, if you are using Access 2010, open the tab File and select the command Save and publish.

On the right in the section Advanced installation click an item Back up the database.

In the dialog box Saving a backup specify a name and location for the backup and click Save.

Access closes the original file, backs it up, and reopens it.

To revert to the backup, close and rename the original file so that you can use the name of the original version. Give the backup a name of the original version and open the renamed backup in Access.

In this section

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

Open the database containing the records you want to update.

In the tab Creation in Group Inquiries press the button Query constructor.

The Query Designer and Dialog Box will open Adding a table.

Open the tab Tables.

Select one or more tables with records that you want to update and click the button Add and then the button Close.

The table or tables appear in Query Designer in one or more windows, and on Windows a list of all the fields in each table. This figure shows the query designer with a regular table.

1. Table in the query designer window

2. Request form

In the table windows, double-click the fields you want to update. The highlighted fields will appear on the line Field request form.

You can add one table field for each column in the query design.

To quickly add all the fields in a table, double-click the asterisk ( * ) at the top of the list of table fields in the table window. The figure shows a request form with all the added fields.



To restrict query results based on field values, in the query design in the row Condition enter the condition you want to use to limit the results.

Condition example table

The table below provides some examples of conditions and the results of their application.

Note: Many of the examples in this table use wildcards to make the query more flexible or efficient.

Condition	Result
	Returns all numbers greater than 234. To find all numbers less than 234, use the condition< 234.
> = "Novosibirsk"	Returns all records from "Novosibirsk" to the end of the alphabet.
Between # 2/2/2020 # and # 12/1/2020 #	Selects dates between 2-Feb-17 and 1-Dec-17 (ANSI-89). If your database uses the ANSI-92 wildcard character set, use hash ( # ) single quotes ( " ). Example: between "2/2/2020" and "12/1/2020".
Not "Germany"	Finds all records in which the contents of the field do not match Germany. According to this condition, records containing additional text other than the word "Germany", such as "Germany (Euro)" or "Europe (Germany)", will be found.
	Finds all records except those that start with the letter "T". If your database uses ANSI-92 wildcards, use the percent sign ( % ) instead of an asterisk ( * ).
	Finds all records that do not end with the letter "t". If your database uses ANSI-92 wildcard characters, use the percent sign (%) instead of the asterisk (*).
In (Canada, UK)	Finds all entries in a list with the words "Canada" or "UK".
	The text box searches for all records that begin with one of the first four letters of the alphabet (A-D). If your database uses ANSI-92 wildcard characters, use the percent sign (%) instead of the asterisk (*).
	Finds all records that contain the letter combination "tr". If your database uses ANSI-92 wildcard characters, use the percent sign (%) instead of the asterisk (*).
Like "Grigory Verny?"	Finds all records starting with the name "Gregory" and containing the second line of 9 letters, of which 6 letters form the beginning of the last name "Verny", and the last letter is unknown. If your database uses the ANSI-92 wildcard character set, use the question mark ( ? ) underscore ( _ ).
	Search all records for February 2, 2020. If your database uses the ANSI-92 wildcard character set, enclose the date in single quotes (") instead of pound signs (#); For example, (" 2/2/2020 ").
< Date() - 30	Uses the function Date to return all dates that are at least 30 days old.
	Uses the function Date to return all records containing the current date.
Between Date () And DateAdd ("M", 3, Date ())	Uses functions Date and DateAdd to return all records between the current date and a date three months later.
	Returns all records containing an empty (unfilled or undefined) value.
	Returns all records containing a non-empty value.
	Returns all records containing a zero-length string. Such strings are usually used when a field must have a value, but this value is not yet known. For example, a field requires a fax number, but some customers do not have one. In this case, you enter a pair of double quotes with no space between them (" " ), not numbers.

1. In the tab Constructor in Group results press the button Execute.

   Make sure the query returns the records that need to be updated.

   To remove fields that you do not want to include in the query designer, select them and press the DELETE key.

   To add fields to the query designer, drag and drop them onto the query design.

Stage 2. Updating records

Updating data in one table with data from another table

When updating data in one table with data from another, a basic rule must be followed: the data types of the source and target fields must be the same or compatible.

Also, if you update data in one table with data from another and use compatible data types instead of matching data types, Access converts the data types of those fields in the target table. As a result, some data in the target fields may be truncated (deleted). This section identifies cases where you can or cannot perform data type conversions. This section also provides a table that demonstrates when a data type conversion can change or delete some or all of the data in a field, and what data can be deleted.

The process of updating data in one table with data from another includes the main steps outlined below.

Create an update query and add the source and target tables to it.

Combining these tables by fields containing related information.

Adding target field names to string Field request form.

Adding source field names to string Update a request form using the following syntax: [ source_table]. [source_field] .

The steps in this section assume that you are using two similar tables. In this example, the Customers table resides in a legacy database and contains more recent data than the Customers table. Several manager names and addresses have been changed. Therefore, it was decided to update the Customers table with data from the Customers table.

Buyers table

Customers table

Remember that although the data types of each field in the table may not be the same, they must be compatible. Access must be able to convert the data in the source table to the data type used in the target table. Sometimes, during the conversion process, some of the data is deleted. For more information on conversion restrictions, see.

Create and execute an update request

Note: The steps below assume you are using the two sample tables above. The described actions can be adapted to specific data.

Data Type Conversion Constraints

The following table provides a list of Access data types and describes the limitations of data type conversions and the potential for data loss.

Executing a request blocked due to content disable mode

If nothing happens when you try to run a change request, check to see if the following message appears on the Access status bar:

This action or event is blocked in shutdown mode.

By default, Access blocks all change requests (update, add, delete, and create a table) unless the database is in a trusted location or is signed and trusted. If any of these conditions are not met, you can enable queries for the current database session by clicking Include content on the message bar.

SQL version: UPDATE statement

If the user is familiar with SQL, you can also write an UPDATE statement in SQL mode. To use SQL mode, create an empty query and switch to SQL mode.

This section provides the syntax for the UPDATE statement and an example of its use.

Syntax

UPDATE table SET new_value WHERE condition;

The UPDATE statement has several parts.

Remarks

The UPDATE statement is especially useful if you need to make changes to a large number of records, or if the records to be changed are in multiple tables.

Several fields can be changed at the same time. In the example below, Orders Amount values ​​are increased by 10 percent and Shipping values ​​are increased by 3 percent for UK vendors.

UPDATE Orders

SET OrderAmount = OrderAmount * 1.1,

Freight = Freight * 1.03

WHERE ShipCountry = "UK";

So far, you've come across queries that perform data fetching and some calculations. However, queries can also be used to add, delete, and update a group of records in an Access database table. Such queries are a powerful tool for transforming data and are called action queries. Suppose that for some reason you need to adjust the dates of contacts by replacing the month of November with December in all records of the List table dating back to 1999. This operation is difficult to do manually if the table contains several thousand records. An action request allows you to quickly solve the problem.

1. In the Access database window, click the button Tables.

2. Select the List table whose data you want to update.

3. In the palette buttons New object select item Inquiry... A dialog box will open New request shown in Fig. 17.8. A similar window opens when you click on the button Create database windows. It allows you to choose the most convenient way to create an object.

Rice. 17.8. Create a request

4. Double click on the line Constructor... The List table that was selected in the database window in step 2 will automatically appear in the query designer window.

5. Drag the field into the request form date, the value of which needs to be updated.

Note The field name, enclosed in square brackets, is a reference to the field value. To refer to a field in another table, you must first specify the table name followed by the field name (both names in square brackets) and separate them with an exclamation point. For example [Contacts]! [Surname].

6. In the palette buttons Request type select item Update... A description of all possible query options offered by Access is given in table. 17.2. The structure of the request form is modified in accordance with the type of request. A field appears in the form of the selected update request option. Update, in which you need to enter the new value of the field. To change the month of the date from November to December, just add 30 days to the date.

7. Enter in the cell Update formula [Date] +30.

TABLE 17.2. Query options

8. To update only dates referring to November 2000, enter in the Criteria cell the formula Between DateValue ("11/30/99") And DateValue ("11/30/99"), which was discussed in detail earlier (Fig. 17.12).

9. Close the request and save it as Update.

10. Click the button Tables database windows and double click on the icon List open this table. It has four entries dating back to November 2000.

Note Action prompts can perform large-scale data changes that cannot be undone. Be careful. Before running such queries, it is useful to make a backup of the database so that you can revert to the original state of the tables. Action request icons have an exclamation mark to warn of their special role.

11. In the database window, click the button Inquiries.

12. Double click on the icon Update.

Answer Yes to the question about the need to start an action request. Access informs you that it has found four records that match the filter criteria and asks you to confirm that you want to change them.

13. Click the button Yes and examine the changes that have occurred to the data in the List table.