Unit 10 . Action queries (II)
Update queries allow us to modify the data stored in a table. In only one operation, all the records can be changed at the same time, or only those that comply with a determined condition.
To create an Update query:
We open a new query in design view.
We add the table that we would like to update.
We drop down the Query menu and select the Update query option.
drop down the button on the toolbar and select the Update query option.
As from this moment the QBE grid changes its appearance, the Sort: and Show: rows have disappeared for lack of significance here and have been replaced by the Update to: row as we can see in the next example.
The source of the query could be a table, a query, or a combination of tables.
In the QBE box we only put the field or fields that intervene in the search criteria and those fields that we want to update.
In the Update to: row we write the expression that calculates the new value to assign to the field.
The expression could be a fixed value, the name of a source field, or any expression based on the source fields, it could also be a parameter.
This expression should generate a value of the appropriate data type for the indicated column.
The expression needs to be calculable from the values of the row that it is updating.
If the column being updated is used in the expression for the calculation, the value used is the one before the updating, the same as for the search criteria.
For the update to affect a part of the records in the table, we need to select the records to update by means of a search criteria. If the query does not contain a search criteria all the records in the table will be updated. In our case we have included the Zipcode = 0 search criteria, and in the Update to: row of the Zipcode field we have type null, which means that the zipcode field will be updated to the null value in those records where it is to zero.
If we update a defined column as part of a relationship, this column can or cannot be updated depending on the referential integrity rules (see Unit 6).
In order to be able to view the data being edited before performing the update we can click on the Design view button on the toolbar or drop down the View menu and select the Datasheet view option.
To run the query click on the button or drop down the Queries menu and select the Run option. When we run the query the changes are performed on the table.
When the value to leave in the field that we are updating is a fixed value, we put it in the Update to: row with nothing more, Access will add inverted commas if the field is text type or # # if the field is date type.
When the value to leave in the field that we are updating is contained in a field of this same table we need to put the name of the field between brackets [ ] so that Access does not confuse it with a fixed value and add inverted commas to it. Lets suppose that we have added a new State field to the table to store the state of each student, and as most of the students live in the capital of the State we want to create a query to fill the State field with location of each student, and later we can manually change those few students who do not coincide with the state.
In the query to create we will need to put [City] between brackets in the state column and the Update to: row so that Access understands that it must get the value from from the City field.
In the Update to: row we can also use an expression based on the field that we are updating or on another field being updated in this query. In these cases the values before the update are used to calculate the expression. E.g if we want to raise the price of our articles by 5%, the expression to type in the Update to: row of the price field will be [price]* 1.05 (this expression is the equivalent of [price] + ([price] * 10/100))
When the value we use is found in another table we need to define the source of the query in such a way that every source row contains the field to update, and the field that contains the value to use for the update. E.g lets suppose that we have added a Residual hours field to the Students table to save the number of hours that each student has left on his/her course. We can create a query to update this field with the hours as we can presume that at the beginning the number of horus left is the same the total hours. In this case the query source needs to contain the residual hours and the hours fields of the course in which the student is recorded. For this reason the Students and Courses tables need to be combined. The query will appear this way:
To practice you can perform the Step by step creating update queries.
Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited. April-2006.