Running Action Queries


All the queries you've worked with to date have been select queries. A select query is one in which Access uses the query criteria to select the matching rows from a table or join. However, Access has several other query types that are designed to perform actions on the data, such as changing values, adding records, deleting records, and writing records to a new table. These so-called action queries are the subject of the next four sections.

Modifying Table Data with an Update Query

Access, like many programs, has a Replace command that enables you to substitute one piece of text for another either in certain records or throughout a table. Although this command often comes in handy, there are some jobs it simply can't handle. For example, what if you want to replace the contents of a field with a new value, but only for records that meet certain criteria? Or what if your table includes price data and you want to increase all the prices by 5%?

For these tasks, you need a more sophisticated tool: an update query. Unlike a select query, which only displays a subset of the table, an update query actually makes changes to the table data. The idea is that you select a field to work with, specify the new field value, set up some criteria (this is optional), and then run the query. Access flashes through the table and changes the field entries to the new value. If you enter criteria, only records that match the criteria are updated.

To create and run an update query, follow these steps:

1.

Create a select query that includes the field (or fields) you want to update and the field (or fields) you'll need for the criteria. (Remember, criteria are optional for an update query. If you leave them out, Access updates every record in the table.)

2.

When the select query is complete, run it to make sure the criteria are working properly.

Handle with Care

Update queries can save you a great deal of time, but they must be approached with caution. After you run an update query, Access offers no direct method for undoing the operation. Therefore, always start off with a select query to make sure your criteria are doing what they're supposed to do. If your data is particularly precious, consider making a temporary copy of the table before running the update query. (To copy a table, click it in the database window, press Ctrl+C, and then press Ctrl+V. In the Paste Table As dialog box, enter a name for the backup table, make sure the Structure and Data option is clicked, and then choose OK.)


3.

Convert the query to an update query by choosing Query, Update Query. (Alternatively, on the toolbar's Query Type drop-down list, click Update Query.) Access changes the query window's title bar to Update Query, removes the Sort and Show rows from the design grid, and replaces them with an Update To row (see Figure 4.21).

Figure 4.21. When you convert a select query to an update query, Access replaces the design grid's Sort and Show rows with an Update To row.


4.

In the Update To cell for the field you want to change, enter the new value.

5.

Run the query. Access displays a dialog box to tell you how many rows (records) will be updated.

6.

Choose Yes to perform the update.

After you see what update queries can do, you'll wonder how you ever got along without them. For example, one common table chore is changing prices and, in a large table, it's a drudgery most of us can live without. However, if you're increasing prices by a certain percentage, you can automate the whole process with an update query.

In Northwind's Products table, suppose you want to increase each value in the UnitPrice field by 5%. To handle this in an update query, you add the UnitPrice field to the design grid and then enter the following expression in the Update To cell:

 [UnitPrice] * 1.05 

This expression tells Access that you want every UnitPrice field entry increased by 5%. You can also set up criteria to gain even more control over the update. Figure 4.22 shows an update query that raises the UnitPrice field by 5%, but only for those records where the CategoryID field is 1.

Figure 4.22. This update query increases the UnitPrice values by 5% for those products where CategoryID equals 1.


Removing Records from a Table with a Delete Query

If you need to delete one or two records from a table, it's easy enough to select each record and choose Edit, Delete Record. But what if you have a large chunk of records to get rid of? For example, you might want to clean out an Orders table by deleting any old orders that were placed before a certain date. Or you might want to delete records for products that have been discontinued. In both examples, you can set up criteria to identify the group of records to delete. You then enter the criteria in a delete query and Access will delete all the matching records.

Follow these steps to create and run a delete query:

1.

Create a select query that includes the asterisk "field" (the asterisk represents the entire table) and any field you need for your deletion criteria.

2.

Enter the criteria and then run the select query to make sure the query is picking out the correct records.

3.

Convert the select query to a delete query by choosing Query, Delete Query. (You can also use the toolbar's Query Type list to click Delete Query.) The title bar changes to Delete Query and Access replaces the design grid's Sort and Show lines with a Delete line. The asterisk field will display From in the Delete cell and each criteria field will display Where in the Delete cell. Figure 4.23 shows a delete query for the Products table that removes all the records where the Discontinued field is set to TRue.

Figure 4.23. A delete query uses the asterisk field and any fields you need for your criteria.


4.

Run the query. Access analyzes the criteria and then displays a dialog box telling you how many records you'll be deleting.

5.

Choose Yes to proceed with the deletion.

Delete Carefully!

If anything, the delete query is even more dangerous than the update query because the records you delete are gone for good and nothing can bring them back. Again, setting up and running a select query first is an easy way to avoid wiping out anything important. (Remember, too, that you can always make a temporary copy of any table that contains particularly important data.)


Creating New Tables with Make-Table Queries

The results of select queries are called dynasets because they're dynamic subsets of the table data. When I say "dynamic," I mean that if you edit the query records, the corresponding records in the table also change. Similarly, if you edit the table, Access changes the query records automatically.

This is usually welcome behavior because at least you know you're always working with the most up-to-date information. However, there might be the odd time when this is not the behavior you want. For example, at the end of the month or the end of the fiscal year, you might want some of your tables to be "frozen" while you tie things up at month's or year's end (this applies particularly to tables that track invoices).

Instead of letting the new work pile up until the table can be released, Access lets you create a table from an existing one. You can then use the new table for your month-end duties, so the old table doesn't need to be held up. You do this using a make-table query.

Here are the steps to follow to create and run a make-table query:

1.

Create a select query that includes the fields you want to include in the new table as well as the field (or fields) you need for the criteria. (The criteria are optional for a make-table query. If you leave them out, Access includes every record in the new table.)

2.

When the select query is complete, run it to make sure the criteria are working properly.

3.

Convert the query to a make-table query by choosing Query, Make-Table Query. (Alternatively, on the toolbar's Query Type drop-down list, click Make-Table Query.) Access displays the Make Table dialog box, shown in Figure 4.24.

Figure 4.24. Use the Make Table dialog box to define your new table.


4.

Use the Table Name text box to enter the name you want to use for the new table.

5.

To create the table in the same database, click Current Database. If you prefer to add the table to an external database, click Another Database and enter the path and filename of the database in the File Name text box.

6.

Click OK.

7.

Run the query. Access displays a dialog box to tell you how many rows (records) will be added to the new table.

8.

Choose Yes to create the new table.

Adding Records to a Table with an Append Query

Instead of creating an entirely new table, you might prefer to add records from one table to an existing table. You can accomplish this with an append query.

Follow these steps to create and run an append query:

1.

Create a select query that includes the fields you want to include in the appended records as well as the field (or fields) you need for the criteria. (The criteria are optional for a make-table query. If you leave them out, Access appends every record to the other table.)

2.

When the select query is complete, run it to make sure the criteria are working properly.

3.

Convert the query to an append query by choosing Query, Append Query. (Alternatively, on the toolbar's Query Type drop-down list, click Append Query.) Access displays the Append dialog box, which is identical to the Make Table dialog box shown in Figure 4.24.

4.

Use the Table Name text box to enter the name of the table to which you want the records appended.

5.

If the other table is in the same database, click Current Database. If the other table is in an external database, click Another Database and enter the path and filename of the database in the File Name text box.

6.

Click OK. Access adds an Append To row to the design grid.

7.

For each field in the design grid, use the Append To cell to choose the field in the other table to use for the append operation.

Using the Asterisk Field

If you add the asterisk field to the design grid, its Append To cell will show the name of the other table. In this case, if you add other fields for criteria purposes, make sure these fields have their Append To cells blank.


8.

Run the query. Access displays a dialog box to tell you how many rows (records) will be appended to the table.

9.

Choose Yes to append the records.



Tricks of the Microsoft Office Gurus
Tricks of the Microsoft Office Gurus
ISBN: 0789733692
EAN: 2147483647
Year: 2003
Pages: 129

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net