|
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.
or,
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