8.2. Update Queries An update query searches for some records, and then modifies them. Usually, you'll limit your modifications to a single field, but Access lets you change as many fields as you want. You also have a fair bit of flexibility in how you apply the update. The simplest option's to stuff an entirely new value into a field. You could create a query that moves all the products in one category into another by entering a new value in the CategoryID field. Alternatively, you could take the current values in a field and change them, using an expression (a specialized database formula that can perform a variety of different calculations). You could increase all your product prices by 10 percent, or add a week to the due date of every outstanding project.
Tip: If you have a relatively straightforward, one-time-only update to make, you may prefer to use the datasheet's find-and-replace feature (Section 3.2.3). This approach gives you the chance to review the matches and choose whether or not to apply the change for each value.
The example that follows uses the Products and ProductsCategories tables from the Boutique Fudge database (which is described in Section 5.4.2). The query updates all the products in the Beverages category, increasing their prices by 10 percent. You can try this example for yourself by downloading the examples for this chapter from the "Missing CD" page at www.missingmanuals.com. Here's how you can create the update query: -
Create a new query by choosing Create Other Query Design . The Show Table dialog box appears. -
Add each table you want to include in your query by selecting it and then clicking Add (just as you did when creating a select query). Click Close when you're finished . Usually, an update query will use a single table. However, if you need information from more than one related table, then add them all. Adding multiple tables creates a join (Section 6.3). Joins work the same in an action query as they do in a select querythey pull information from a parent table, and then display it alongside the records from the child table. In this example, you need the Products and ProductCategories tables. -
Change your query to an update query by choosing Query Tools Design Query Type Update . The column list at the bottom of the window changes to reflect your new type of query. The Sort and Show boxes disappear (because they have no meaning in the world of update queries), and a new Update To box appears for every field that's included in the query. -
Add the field (or fields) you want to use for filtering, and then set the Criteria box for each one . Your filter conditions determine what records Access selects. Since this query's an update query, the records you select are the records you'll end up changing. In this example, you need to use the CategoryID field or CategoryName field. If you use the CategoryID field, you need to supply the ID value for your category. If you use the CategoryName field, you can match records using the descriptive name . To add a field, double-click it in the table box, just as you would with a select query. Then set the criteria to the value you want to match, as in Figure 8-1. If you want to apply an update to all the records in a table, then you don't need to set any filter criteria. -
Add the field (or fields) you want to change . In this example, you need to add the Price field so you can modify the product prices. -
In the Update To box, supply the new value that your query will apply to each field . You have two options for updating a field. You can apply a fixed value by typing it into the Update To box. If you take this approach, Access updates every record you select with the exact same value. You can also use an expression that takes one or more existing fields values, and uses them to calculate a new value. You can use all the operations and functions described in Chapter 7 to manipulate text, numbers , and dates. You can, for instance, use the following expression on the Price field to ratchet up product prices by 10 percent: [Price]*1.10 | | Figure 8-1. This query matches products in the Beverages category. | |
Tip: You can also use one or more parameters (Section 7.4) in your update expression. That way, Access prompts the person running the query to supply critical information (like the percentage to use for changing the price).
-
Add any other fields that you want to inspect to confirm that you're selecting the correct records . Before you run your query and apply your changes, you perform a preview that displays all the rows your update query will select (and thus, all the records it'll change when you run it). In order to confirm that your query's grabbing the right records, you may need to see some other identifying information in the datasheet grid, like the ProductName. However, there's one bit of bureaucratic trickery you need to perform to make this preview work. Access ignores fields that you don't plan to update. So if you want to make sure the ProductName field appears in the datasheet preview, then you need to supply something in the Update To box. In this case, use the value [ProductName]. This step tells Access to update the ProductName field with the current value of the ProductName field. In other words, Access doesn't actually change anything, but it shows the ProductName field in the datasheet preview. Figure 8-2 shows the finished update query. | | Figure 8-2. This query matches all the products in a specific category and raises the price 10 percent. | | -
Right-click the query's tab title, and choose Datasheet View to see the rows that your query affects (Figure 8-3) . This step lets you preview the rows you're about to change, before you actually pull the trigger. In the datasheet, you see all the records that match your query's filtersin other words, all the records you'll change when you run the query. However, you won't see the changes you want to make.
Note: In a basic select query, viewing the datasheet and running the query are equivalent actions. In an action query, viewing the datasheet shows you the rows that'll be affected, but doesn't actually change them. Running the query performs the modification, but doesn't show you the changed records.
| | Figure 8-3. Here's the datasheet preview. It shows all the products in the Beverages category, with the current prices. When you run this query, these are the records that will be changed. | | -
Now switch back to Design view (right-click the tab title, and then choose Design View). If you're confident you've got your query right, choose Query Tools Design Results Run to run your update query and have Access apply your changes . Remember, it's always a good idea to back up your database (Section 1.3.1) before you take this step. When you run an action query, Access warns you about the change it's about to make (Figure 8-4). Click Yes to make the change. Sadly, Access doesn't show you the updated recordsin fact, it doesn't show you anything. If you're wondering what happened , you may want to review the records you just changed. One way to do this is to show the preview for your update query again (by right-clicking the tab title, and then choosing Datasheet View). This method works as long as you haven't changed the records in such a way that they no longer match your filtering conditions. (If you have, you'll need to create a new query or browse the table to double-check your data.) | | Figure 8-4. Top: Every time you run an action query, Access warns you that the query will change your database. If you don't need this reminder, follow the steps in this window to open the Access Options dialog box and turn off this warning. (You'll need to click No to close the dialog box first.) Bottom: Next, Access tells you how many records will be affected and gives you a last-minute chance to back out. Access always gives you this information, even if you turn off the standard warning. If you click Yes now, then Access updates your table. | | -
If you want to save your query, then hit Ctrl+S (or close the query tab). You'll need to supply a name for your query . Consider using a query name that clearly indicates that this is an action query. You may want to use a name like UpdateProductPrices. Action queries show up with an exclamation-mark icon in the navigation bar. Each type of action query has a slightly different iconfor update queries, you'll see a pencil with an exclamation mark beside it (Figure 8-5). | TROUBLESHOOTING MOMENT When Access Blocks Your Update | | Consider this bit of Access existential philosophy: What happens if you click Run (as described in step 9 in Section 8.2), and nothing happens? There's no warning, no message box, and no error to explain what went wrong. All you get is a cryptic message in the Status bar at the bottom of the Access window, which blandly advises you, "The action or event has been blocked by Disabled Mode". What does it all mean? Access is a truly paranoid program. It's just not ready to let you perform some actions unless you explicitly say it's OK. As you learned in Section 1.3.3, every time you open your database, Access shows a message bar with a security warning. It's up to you what you do about that security warning. You can click the X in the top-right corner to hide the message bar altogether. In this case, your database remains in a slightly disabled state. You can still create, modify, and delete database objects on your own. However, you can't run any code or action queries. (To get the message bar back and see the security warning again, choose Database Tools Show/Hide Message Bar.) Your other choice is to click the Options button in the message bar to show the Microsoft Office Security Options dialog box. Then, choose "Enable this content", and then click OK. This action gives Access your ironclad guarantee that the database is safein other words, it hasn't been created by some Cheeto-munching hacker in his parents' basement . Once you take this step, Access allows you to run action queries (at least until you close the database and open it again, at which point the security warning reappears). If you're getting tired of turning on your database every time you use it, there's a solution. You can tell Access to trust all the databases in a specific folder on your hard drive. This technique's explained in Section 15.2.4. | If you don't plan to use your query again, then consider deleting it. Deleting it prevents you (or someone else) from accidentally rerunning the query and applying changes you don't want. | | Figure 8-5. Remember, every time you double-click a query in the navigation pane, you run it. If the query you click is an action query, like the one highlighted here, you may have just updated or deleted some important data. (To open an action query without running it, right-click it, and then choose Design View instead.) | | |