Russian University of Cooperation

Department of Information Technology

Discipline "Informatics"

Prepared by Associate Professor Gudzenchuk Z.V.

Subject. DBMS Access - update queries

General information about requests

Requests are used to view,. sorting, filtering, modifying and analyzing data in various ways. Queries can also be used as record sources for forms, reports, and data access pages

In Access, queries can be created in two modes:

QBE(Query By Example) - sample queries, the parameters of which are set in the query designer window - query form;

SQL(Structured Query Language) - a structured query language that uses SQL operators and functions to create them.

In Access, it's easy to switch a query from QBE into mode SQL- request:

In table view The query displays the result of the query execution for viewing.

Request types

In Design view, the query type is selected by clicking on one of the buttons in the group Request type(Table 1).

Table 1

Request button	Request type	Destination request
		Create a query that fetches records from a database and displays them.
	Create a table	Create a query that selects records from a database and saves them as a new table
	Addendum	Create a query that adds data to an existing table
	Update	Create a query that changes data in an existing table
	Cross	Create a query that pivots data across two sets of values, one of which appears on the left side and the other on the top of it.
	Removal	Create a query that removes data matching specified conditions from an existing table
	Union	Create a query that organizes the receipt and viewing of data from several related tables.
	Server request	Creating a query in SQL mode. When using linked tables in server databases such as SQL Server or Oracle, you may need to run a query on the server that is more powerful than the "native" SQL Access language supported by the server.
	Control	Create a data definition query (control queries) in SQL mode. You can use the CREATE TABLE (create a table) and ALTER TABLE (change the table structure) statements in an Access control query.

Change request type

Change Request is a query that allows, by performing one operation, to make changes to many records. There are four types of change requests:

table creation

deleting records

adding records

updating records.

The listed types of change requests allow you to automatically create new tables or change base tables in accordance with the data in the resulting tables.

Update requests are mainly used to make changes to a large number of records at once with a single request. A classic example of such a request is to change the price in the Products table by some constant factor, 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 label tables(Tables) database windows.
2. Select the Products table. Left click on the arrow on the button New object(New Object) on the toolbar and select a value from the list Request(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 condition for selecting records: for example, in the "TypeCode" column, enter the value 1.
4. Run a query to ensure that all records containing drinks are selected.
5. Now let's change the query, turning it into an update query. To do this, execute the menu command Request, Update(Query, update). The request header changes and an additional line appears Update(Update To). This makes the lines disappear. Sorting(Sort) and Output on display(Show) (Fig. 8.8).
6. Now you need a 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 we can execute the request. To do this, click the button launch(Run) on the toolbar. Just like when adding records to a table, Access displays a message about the number of records being updated and asks for confirmation of the update. You can choose to approve or reject the update of records.

Rice. 8.8.

Before executing the update request, left-click on the arrow on the button View(View) and select Table view(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 example above, the values ​​were changed in a field that is not the primary key in the Products table. A special case occurs when you want to update the value of a primary key in a table. If this table is related in a one-to-many relationship with other tables, then changing the primary key of a record must change the foreign key values ​​in all related records in the subordinate tables at the same time. Access makes this change happen automatically because it supports cascading updates to records. When defining a relationship between tables, you can check the box (Cascade Update Related Fields) .

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

You can see how such a query works by looking at the tables "Employees" (Employees) and "Orders" (Orders). To do this, we will first have to make copies of these tables and establish a relationship between them:

1. Expand the list of tables in the database window and select the "Employees" table.
2. Copy the table to the clipboard by pressing the key combination + .
3. Paste the table from the clipboard by pressing the keyboard shortcut + . A dialog box will appear Inserting a table(Paste TableAs).
4. In field Table name(Table Name) enter the string: Employees (copy). Leave the default value of the radio button in the group Paste options(Paste Options). Click the button OK or key . The new table will appear in the list.
5. Follow steps 1-4 for the Orders table by creating the Orders (Copy) table.
6. To run the example, we will have to slightly modify the "Orders (copy)" table. The EmployeeID field in the Orders table is a lookup field, meaning that although this field contains employee IDs, when the table is displayed, this field shows the employee's last name and first name. We can temporarily remove the lookup field so that we can see the results of the cascading update of that field. To do this, just open the "Orders (copy)" table in Design mode, select the "EmployeeCode" line, open it in the panel Field Properties(Field Properties) tab Substitution(Lookup) and set property value Row source type(Row Source Type) equal to Field(Textbox). Save the change.
7. Another change will need to be made to the "Employees (copy)" table. The field "EmployeeID" in it has the type Counter(AutoNumber), so Access won't let you change the value in that field. Open this table in Design view and change the data type for the "EmployeeID" field to Numerical(number).
8. Now we need to establish relationships between the new tables. Click the button Data Schema(Relationships) on the toolbar to display the window Data Schema(Relationships).
9. Click the button Clear Layout(Clear Layout) to clear the data layout window. Confirm your intention in the message box by clicking the button Yes(Yes).
10. Add tables "Employees (copy)" and "Orders (copy)" using the dialog box Adding a table(Show Table). To do this, click the corresponding button on the toolbar.
11. To establish a one-to-many relationship between tables, drag the EmployeeID field of the Employees (copy) table to the corresponding field in the Orders (copy) table. A dialog box will appear Change links(Edit Relationship).
12. In this window, you need to check the boxes Ensuring Data Integrity(Enforce Referential Integrity) and cascading update of related fields(Cascade Update Related Fields) (Fig. 8.9). Click OK.
13. Close the window Data Schema(Relationships).

Update requests are mainly used to make changes to a large number of records at once with a single request.

Example

As an example, consider a query that changes the cost of courses in the Kursused table by some constant factor, such as increasing the cost of all courses by 20%.
To create such a request:

1. In Design mode, select the fields Kursuse_kood, Nimetus, Maksumus from the Kursused table (Curses) in the query.

2. Run a query to ensure that all records are selected.

3. Now let's change the request, turning it into an update request. To do this, click on the button Update. The request header changes and an additional line appears (Update). This makes the lines disappear. Sort (Sorting) and show (Output on display).

4. Now you need to line Update To enter an expression by which the new cost will be calculated: * 1, 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.

5. Before executing the update request, left-click on the arrow on the button View and select

An update query can be used to update data in table fields. Changes are made to a group of records 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, which is actually a client of InvestCapitalBank, was listed as a client of VTB 24. To update the data, select New > Constructor > Tables > Clients (Fig. 37).

Figure 37 - Creating an Update Request in Design view

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

Figure 38 - Selecting the request type

We fill in the request by selecting Field - Name, Table name - Client, Update - InvestCapitalBank, Selection condition - VTB 24 and specifying 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

So far, you've encountered queries that perform data fetching and some calculations. However, queries can also be used to add, remove, 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 all records in the List table relating to 1999 from the month of November to December. Such an operation is difficult to do manually if the table contains several thousand records. An action request allows you to quickly solve the task.

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

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

3. In the button palette New object select item Request. A dialog box will open New request shown in fig. 17.8. A similar window opens when the button is clicked. Create database window. 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 selected in the database window in step 2 will automatically appear in the query designer window.

5. Drag the field into the request form the date The whose value is 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, and then the field name (both names in square brackets) and separate them exclamation mark. For example [Contacts]! [Surname].

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

7. Type in a cell Update formula [Date] +30.

TABLE 17.2. Request Options

Type	Description
Sample	Selecting data in the query result table based on the specified selection criteria
Cross	The query result outputs the statistical values ​​(sum, count, or average) for one of the table fields, depending on two parameters of the other table fields that specify the row and column headings of the cross-query result
Create a table	Creating a new table in the current or in another database based on information from existing tables
Update	Update table data
Addendum	Adding a Recordset to a Table
Removal	Deleting table entries according to specified criteria

8. To update only dates related to November 2000, enter the formula Between DateValue ("1.11.99") And DateValue ("30.11.99") in the Criteria cell, which was discussed in detail in earlier (Fig. 17.12).

9. Close the query by saving it as Update.

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

Note Action requests can make large-scale changes to data that can no longer be undone. Be careful. Before running such queries it is useful to do backup databases to be able to return to the original state of the tables. Action request icons have an exclamation mark to alert them to their special role.

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

12. Double click on the icon Update.

Reply Yes to the question about the need to run an action request. Access informs you that it has found four records that meet the filter criteria and asks you if you want to change them.

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