Action queries: manipulating your data.
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.
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.
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 firstname.lastname@example.org.
|Printer friendly Cite/link Email Feedback|
|Date:||Jul 1, 2009|
|Previous Article:||SMALL and LARGE functions.|
|Next Article:||Tools of the trade.|
|Adult Brain Attack Database.|
|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.|