Printer Friendly

Action queries: manipulating your data.

Queries are a very powerful feature of database products. In January 2009, we covered select queries, which can be used to gather data for reports. Queries can also be used to manipulate data. For example, they can be used to add records at the end of an existing table, to update data in an existing table, or to delete data from a table. All queries in Access begin as select queries, but they can be changed to action queries using the Query Tools Design Ribbon. Three commonly used action queries are append, update, and delete queries.

Append Query

If you want to import data from one table into another, this would be accomplished with an append query. Create a query that selects the data you want to add to another table (see January 2009 for a refresher on creating a query). On the Query Tools Design Ribbon, click the Append button. This opens the Append dialog (see Figure 1), where you can choose the table the records will be added to. You can choose either a table in the current database or in another database. In this example, discontinued product records selected from the Product table will be appended to the Product History table.

[FIGURE 1 OMITTED]

After clicking OK and closing the dialog, you can save and name the query if you plan to use it again in the future. I recommend using the word "Append" at the beginning of the name, then identify what it appends and to where. For example, for a query that appends discontinued product sales information to the table containing the past products, I suggest using a name such as "Append Discontinued Products to Product History Table." If the names of the fields used in your query are the same as the fields in the target table, the Append To line in the query-by-example (QBE) grid will show the field names. Otherwise, use the dropdown box on the Append To line to select the corresponding fields in the target table. Now run the query to append the records. A warning will appear letting you know how many records you will append. If the number seems incorrect, you can answer "No" to cancel. Once you select "Yes," the records will be appended to the table, and you can't use the Undo function to go back.

Delete Query

If you want to delete data from a table, use a delete query. First, create a query that selects the data you want to delete. If you want to delete the values in all the fields, simply drag the asterisk (*), which appears above the first field name in the table window, to the QBE grid. Then click the Delete button on the Query Tools Design Ribbon. Save the query with a descriptive name if you want to use it each month. If you're deleting all of last month's data before importing new data, you might want to name it something like "Empty--[Table Name]." Figure 2 presents an example that deletes all records from the Sales Information Table.

[FIGURE 2 OMITTED]

If you don't want to delete all the records, you need to include criteria on the Criteria line to limit the records. In essence, you create a select query to make sure you have only the records you want to delete. Then click the Delete button to change the select query into a delete query.

Update Query

Another typical process is to update the values in a field, which is done with an update query. For example, let's say you want to increase product sales prices by 5% for the new year. Begin by creating a query that includes the field you want to update. Then click the Update button on the Query Tools Design Ribbon. This will change the QBE grid to show an Update To line instead of the Sort line that appears for Select queries. To change the values of the field, add an expression in the Update To line. The field name is entered in square brackets, and the calculation symbols are the same as you would use in Excel or other products. In Figure 3, for example, the Update To line contains [Suggested Retail Price]*1.05.

[FIGURE 3 OMITTED]

When you run the query, it will update the values in the selected fields with the Update To value or expression result. As with the append query, it will warn you about the number of records about to be updated and give you a chance to select "No" to stop the process.

Next month we will create navigation forms to put all of this work together into a system. Following that, we will look at creating a process that combines a series of queries or other actions together to make an automated process.

Patricia Cox teaches Excel and Access to management accounting students at Alverno College in Milwaukee, Wis., and has consulted with local area businesses to create database reporting systems since 1998. She is also a member of IMA's Greater Milwaukee Chapter. To send Patricia a question to address in the Access column, e-mail her at kathrynmann@tds.net.
COPYRIGHT 2009 Institute of Management Accountants
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 2009 Gale, Cengage Learning. All rights reserved.

Article Details
Printer friendly Cite/link Email Feedback
Title Annotation:ACCESS
Author:Cox, Patricia
Publication:Strategic Finance
Geographic Code:1USA
Date:Jul 1, 2009
Words:858
Previous Article:SMALL and LARGE functions.
Next Article:Tools of the trade.
Topics:


Related Articles
Adult Brain Attack Database.
Beginning SQL.
Visual Basic 2008 recipes; a problem-solution approach.
Select queries: using your data to answer business questions.
Access data analysis cookbook.
Basic Access macros.
Make table queries.

Terms of use | Privacy policy | Copyright © 2019 Farlex, Inc. | Feedback | For webmasters