New book: Microsoft Access 2010 Inside Out

Hello. This is Kenyon again. We’re excited to announce that Jeff Conrad and John Viescas’s Microsoft Access 2010 Inside Out (ISBN: 9780735626850; 1488 pages) is now available for purchase!  

You can find the book’s chapter-level table of contents and an introduction to this in-depth guide to Microsoft Access in this previous post.

The book includes a companion disc that includes sample database applications, bonus chapters and technical articles, and a compete ebook.

In today’s post, please enjoy a deeper look at Chapter 11, “Modifying Data with Action Queries.”

Chapter 11

Modifying Data with Action Queries

In Chapter 9, “Creating and Working with Simple Queries,” you learned how to insert,
update, and delete single rows of data within a datasheet. In Chapter 10, “Building Complex
Queries,” you discovered that you can use queries to select the data you want—
even from multiple tables. You also learned under what conditions you cannot update a
field in a query. Now, you can take the concept of queries one step further and use action
queries to change, insert, create, or delete sets of data in your database quickly.

The four types of queries you’ll study in this chapter are

• Update query   Allows you to update the fields in one or more rows.
• Make-table query   Allows you to create a new table by selecting rows from one or
  more existing tables.
• Append query   Allows you to copy rows from one or more tables into another table.
• Delete query   Allows you to remove one or more rows from a table.

Note   The examples in this chapter are based on the tables and data in HousingDataCopy.accdb
and ContactsDataCopy.accdb on the companion CD included with this book. These
databases are copies of the data from the Housing Reservations and Conrad Systems
Contacts sample applications, respectively, and they contain the sample queries used in
this chapter. The query results you see from the sample queries that you build in this
chapter might not exactly match what you see in this book if you have changed the
sample data in the files.

Updating Groups of Rows

It’s easy enough to use a table or a query in Datasheet view to find a single record in your
database and change one value. But what if you want to make the same change to many
records? Changing each record one at a time could be very tedious.

Remember that in Chapter 9, you learned how to construct queries to test proposed new
validation rules. In the HousingDataCopy.accdb database, there’s a table-level validation
rule defined in tblFacilityRooms that doesn’t let you enter a WeeklyRate value that
is greater than seven times the DailyRate value. If you want to change this rule to ensure
that the WeeklyRate value is no more than six times the DailyRate value (thereby ensuring
that the weekly rate is a true discount), you must first update the values in the table to
comply with the new rule.

\You could open tblFacilityRooms in Datasheet view and go through the individual rows
one by one to set all the WeeklyRate values by hand. But why not let Microsoft Access
2010 do the work for you with a single query?

Testing with a Select Query

Before you create and run a query to update many records in your database, it’s a good
idea to first create a select query using criteria that select the records you want to update.
You’ll see in the next section that it’s easy to convert this select query to an update query
or other type of action query after you’re sure that Access will process the right records.

You could certainly update all the rows in tblFacilityRooms, but what about rows where
the WeeklyRate value is already less than or equal to six times the DailyRate value? You
don’t want to update rows that already meet the proposed validation rule change—you
might actually increase the WeeklyRate value in those rows. For example, a room might
exist that has a DailyRate value of $50 and a WeeklyRate value of $275. If you blanket
update all rows to set the WeeklyRate field to six times the DailyRate field, you’ll change the
WeeklyRate value in this row to $300. So, you should first build a query on tblFacilityRooms
to find only those rows that need to be changed.

Open HousingDataCopy.accdb and start a new query on tblFacilityRooms. Include the
FacilityID, RoomNumber, DailyRate, and WeeklyRate fields. Enter the criterion >[DailyRate]*6
under the WeeklyRate field. Your query should look like Figure 11-1.

When you run the query, you’ll see 276 records that you want to change, as shown in Figure
11-2. (There are 306 records in the table.)

Converting a Select Query to an Update Query

Now you’re ready to change the query so that it will update the table. When you first create
a query, Access builds a select query by default. You can find commands for the four
types of action queries—make-table, update, append, and delete—in the Query Type
group of the Design contextual tab below Query Tools, as shown in Figure 11-3. (Switch
back to Design view if you haven’t already done so.) Click Update to convert the select
query to an update query.

When you convert a select query to an update query, Access highlights the Update button
in the Query Type group when the query is in Design view and adds a row labeled Update
To to the design grid, as shown in Figure 11-4. You use this row to specify how you want
your data changed for those rows that meet the query’s criteria. In this case, you want to
change the WeeklyRate value to [DailyRate]*6 for all rows where the rate is currently too
high.