Database Reference
In-Depth Information
Updating records by using queries
As you use a database and as it grows, you might discover that errors creep in or that some
information becomes out of date. You can tediously scroll through the records looking for
those that need to be changed, but it is more efficient to use the tools and techniques pro-
vided by Access for that purpose.
If you want to find or replace multiple instances of the same word or phrase, you can use
the Find and Replace commands in the Find group on the Home tab. These commands
work much like the same commands in Microsoft Word or Microsoft Excel.
If you want to manipulate information stored in the database only under certain circum-
stances, you need the power of an action query. An action query finds records that match
the selection criteria and performs an action on them. For example, you can increase the
price of all products in one category by a certain percentage, or remove all the items be-
longing to a specific product line. This type of data manipulation is easy to do with an action
query. Not only does using a query save time, but it helps to avoid errors.
Four types of actions are available:
▪
Append
Adds records from one or more tables to the end of one or more other
tables.
▪
Delete
Deletes records from one or more tables.
▪
Make-table
Creates a new table from all or part of the data in one or more tables.
▪
Update
Makes changes to records in one or more tables. Running an update query
makes irreversible changes to the underlying table, so you should always create a
backup copy of the table before running this type of query. You can quickly create a
copy of a table by displaying the
Tables
list in the
Navigation
pane, clicking the table
you want to copy, pressing
Ctrl+C
, and then pressing
Ctrl+V
to paste a copy. In the
Paste Table As
dialog box, enter a name for the new table, and then click
OK
. The
backup table then becomes part of the database. You can delete it when you are sure
that the update query produced the results you want.
TIP
In addition to these queries, you can create SQL queries, including union, pass through,
and data deinition queries. SQL queries are beyond the scope of this topic.
