While finding and organizing the information stored in your database is an immensely useful task, queries are capable of doing much more. They can actually perform specified tasks for you and make managing your data a great deal easier. Queries that perform jobs for you are known as action queries because they do things with or to the information stored in your tables. A query can look for and modify records that have an unmatched field between two related tables, update records stored in your tables, delete specified records from one or more tables, create a new table for you, or even add records to an existing table. All of these jobs would take a good while to do manually, but a carefully designed query can accomplish them in a matter of seconds.
An action query can help whenever you want to make bulk changes to your dataóchanges that affect an entire group of records in a similar manner. Suppose that you work for a mail order company and want to increase all of your prices by eight percent. It is much easier to create an Update query to calculate the change and update each record than to manually do the calculations and change the field values.
Access provides four types of action queries:Anytime you create a new query it is a select query until you tell Access to make it something else. When you run a select query, Access displays the dynaset, which you can then view and make changes to record by record. An action query saves you time by allowing you to make changes to large groups of records at the same time. If you perform a series of changes frequently, then you can save even more time by writing a macro that runs each of the action queries in succession at the click of a button. The key thing to remember with action queries is that they are not working with a dynaset, but are actually changing the records stored in your tables. For this reason, you should always run a select query to make sure that you have the information you want before you run an action query. The same criteria selection rules apply to action queries as to select queries.
- A Make-Table query creates a new table from all or part of another table or tables.
- A Delete query deletes records from a table or tables.
- An Append query adds a group of records to an existing table or tables.
- An Update query changes the data in a group of records.
To ensure that you do not make unintentional or unwanted changes to your database by inadvertently running an action query, Access displays different icons for each type of query in the database window. An action query will always be followed by an exclamation point icon. When you open an action query, Access also prompts you to confirm that you want to run the action query and tells you how many records will be changed. This is intended as a safety mechanism to prevent accidental deletions from your tables. If you do not wish to be prompted each time, then choose Options from the View menu and set Confirm Action Queries to No.
+ vs. &: What's the difference? When you're building these original select queries, whether or not you plan to adapt them into action queries, you may run across situations where the data won't cooperate. For example, if you wish to create a FullAddress result from StreetNo, StreetName, and StreetType fields, the straightforward way is to create the value StreetNo & "" & StreetName & "" & StreetType. This, however, will result in leading spaces in situations where there is no StreetNo available. Access offers you a solution with the conditional concatenator +. The + will check the existence o the item preceding it. If that item does exist, it behaves like the &, but if that item does not exist, then the + does not add on the following item. So, in this case we can rewrite the value to StreetNo + "" & StreetName & "" & StreetType. Voila! If we don't have a StreetNo, the leading space does not get added to the resultant string.
A make-table query creates a new table by retrieving the records you asked for and using them to make a new table. You can choose the fields from one or more existing tables to include in the new table and can also specify the criteria that must be met from each field. Examples of how you can use this type of query include making a backup copy of a table, creating a table that contains records older than a specified date, creating reports that display data from a specified point in time, and improving the performance of forms and reports that are based on multiple tables.
- Create a query, selecting the tables or queries that contain the fields you want included in the new table.
- From the Query menu, choose Make Table or click on the Make Table button on the toolbar.
- In the Query Properties dialog box do each of the following:
- Enter the name of the table you wish to create, or if you want the new table to replace an existing table in the current Access database, click the arrow and choose a name from the list.
- Indicate whether the new table is located in the current database or another database. If you choose another Access database, provide the filename and the path. You can also identify a path to a FoxPro, Paradox, dBase, or Btrieve database.
- Choose OK.
- Add the fields you want to include in the new table and set any criteria.
You should always preview the results of your query before the query is actually run. Following the "ounce of prevention is worth a pound of cure" adage, you want to see the results before you execute the query.
- Choose Datasheet from the View menu (or click the Datasheet View button on the toolbar).
- Switch back to Design view and make changes to your query.
- With the query open in Design view, choose Run from the Query menu or click the Run button on the toolbar.
- Access will then display a dialog box showing the number or records to be affected, and you can complete the query by clicking on OK.
After you run a make-table query, you will probably want to switch to the new tableís Design view, set a primary key, and establish more accurate table properties. The fields in the table you have created will inherit the same data types and field sizes as those in the original tables. The new table does not, however, contain a primary key, and none of the other table or field properties is inherited.
To remove a number of records that meet the same criteria, it is much faster to use a Delete query than to delete each record separately. For example, you can remove people from your mailing list if they have not purchased a product within the past six months. With delete queries, you remove entire records rather than selected fields within records. In many cases, you can delete records from only one table. If your database contains two tables with a one-to-many relationship, you can delete from one side of the relationship without affecting the table on the other side. In other instances, if you delete records from either side of the relationship, it will delete records in both tables. This simultaneous deletion occurs when your query contains a table that is on the ìoneî side of the one-to-many relationship and the referential integrity has been set with the Cascade Delete option.
- Create a select query, selecting the table from which you want to delete records and any other tables that you want to use for establishing criteria. It is always a good idea to double-check the existing relationships in your tables before you create the query.
- From the Query menu, choose Delete or click on the Delete Query button on the toolbar.
- After Access changes the title of the query window to Delete Query , drag the following fields to the QBE grid :
- The asterisk field for the table that you want to delete records from. ìFromî will appear in the Delete cell under this field.
- The fields that you want to specify criteria from. ìWhereî will appear in the Delete cell under these fields and indicates that you are using these fields to set the deletion criteria.
- Specify your criteria if necessary.
- Preview the records by choosing the Datasheet View button.
- Choose Run from the Query menu or click on the Run button on the toolbar.
- A dialog box appears showing the number of rows to be deleted. Complete the query by choosing OK.
An append query is an action query that adds records from one or more Access tables to another existing table. As with make-table queries, you can append records to a table in the current database or in another database. For example, suppose your mail order company gets a new list of target customers and you want to add that list to your existing Customers table. To avoid typing all of this new information into your database, you would instead append the information to your existing database. Append queries are also useful when you only want to add records that match specific criteria, such as customers with outstanding orders or university alumni who have agreed to attend an alumni banquet in Atlanta.
- Create a query, selecting the table or tables that contain the records you want to append to another table.
- From the Query menu, choose Append, or click the Append Query button on the toolbar.
- In the Query Properties dialog box do each of the following:
- Enter the name of the table to be appended to.
- Indicate whether the new table is located in the current database or another database. If you choose another Access database, give the filename and the path. You can also identify a path to a FoxPro, Paradox, dBase, or Btrieve database.
- Choose OK.
- Drag the following fields to the QBE grid :
- All the fields you want to append;
- Any fields you want to use for setting criteria; and the field that corresponds to the primary key field of the table to which you are appending records. The key field may not be necessary if the field has a Counter data type.
- Or, as an alternative, you can drag the asterisk if you want to append all fields that have the same name as fields in the table you are appending to.
- Specify any criteria that you need.
- In the Append To row, enter the names of the fields to append to. To enter the name of a field, click in its Append To cell, and then click on the arrow to see a list of fields in the table youíre appending to. Select the field you want to append to. If you chose the asterisk in the Field cell, Access chooses the asterisk in the Append To cell. Choosing the asterisk in both places tells Access to append all fields with matching names and discard all non-matching fields. If the field names in the table you are appending to are the same as the names in the table you are appending from, the Append To row automatically displays the field names.
- Choose Datasheet View from the toolbar to preview the records that will be appended before you run the query.
- Choose Run from the Query menu or click on the Run button on the toolbar.
- After Access displays a dialog box showing the number of rows to be appended, complete the query and append the records by choosing OK.
With an update query, you can change data in existing tables. For example, you can raise the prices on items in a catalog by eight percent or you can raise salaries by five percent for people within a certain job category. You can manually update tables through a form or datasheet, but update queries allow you to update large quantities of information in a single action.
- Create a query by selecting the tables or queries that include the records you want to update and the fields you want to use for establishing criteria.
- From the Query menu, choose Update, or click on the Update Query button on the toolbar.
- Drag the fields you want to update or for which you want to specify criteria to the QBE grid and set the criteria you need.
- In the Update To cell for the fields you want to update, enter the expression or value you want to use to change the fields.
- Preview the results of the query by choosing the Datasheet View button before you run the query.
- In Design view, choose Run from the Query menu or click the Run button on the toolbar.
- After Access displays a dialog box showing the number of rows to be updated, choose OK to complete the query and update the records.
A crosstab query presents a large amount of summary information in an easy to understand form and makes comparisons and data analysis easy. A crosstab query is very useful as the basis for a report that groups and totals your data. You can use the Crosstab Query Wizard to create a crosstab query, but there are times when you need to be able to design your own. In order to create a crosstab query, you must specify which field(s) is (are) used for row headings, which field is used for column headings, and which supplies the values.Note: the dynaset provided by a crosstab query is a snapshot and is not updatable.
- Create a query without using the Query Wizards .
- Select the tables or queries that you want to add.
- Drag the needed fields to the Field row of the QBE grid and specify criteria.
- From the Query menu, select Crosstab. The Total and Crosstab rows will display on the QBE grid .
- Click the Crosstab cell for the field to be used as the row heading, click the arrow, and select Row Heading from the list. You can choose more than one row heading, but you must specify at least one.
- Click the Crosstab cell for the field you want to use as the column heading, click the arrow, then select Column Heading from the list. The Total cell for both column and row headings must be set to Sum.
- Click the Crosstab cell for the field from which you want to take summary values, click the arrow, and then select Value from the list. In a crosstab query, you can have only one field set to Value.
- Click the Total cell for the field from which you want to take summary values, click the arrow, and select the type of total you want from the list. The field cannot be anything other than a summary field and must contain a total.
- Choose Run from the Query menu or click on the Run button on the toolbar.