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