Access Data Analysis Cookbook

Chapter 3. Action Queries

There are passive queries and there are action queries. A passive query, such as the standard select, pulls data into a result set, but does not alter any data (either in the source, or by virtue of persisting the returned data past the time the query has been run and left active).

Action queries, on the other hand, can alter source records and persist the returned records indefinitely. A delete query, for example, removes records from source tables—a completely destructive procedure. If the data hasn’t been backed up, it’s gone for good. Update queries also affect source data by changing the existing data. Again, unless the original data is backed up before the operation is carried out, it’s irrecoverable.

Append and make-table queries are types of action queries that do not alter source data but do persist the returned records into permanent results. An append query places returned records into an existing table, and a make-table query places returned records into a new table. We’ll explore all of these types of queries in this chapter.

Running an Update Query

Problem

I need to edit the data in my table. The State field contains two-character acronyms, but I need to change these to the full state names. How can I do this?

Solution

Update Query is one of the types you can select when designing a query. Use the Query → Update Query menu option to prepare the grid.

In Access 2007, create a blank grid using the Ribbon, and select the Update query type in the query ...