Understanding Access Query Types

Before this lesson ends, you should spend a little time learning about the different types of queries that Access offers. In this lesson, you created a simple select query that "selects" data from a table or tables based on your query criteria. You can also build other types of queries in Access and most are based on the select query. For example, a select query pulls certain data from a table or tables. If you wanted to make a table from the data that the query pulls together for you, all you would have to do is create the select query and then change it to a Make Table query. The query type is changed in the Query Design view using the Query menu. We will work in the Query Design view in the next lesson.

The different query types are

• Make Table Query This type of query is similar to a select query, but it takes the data pulled together by the criteria and creates a new table for the database.

• Update Query This query updates field information in a record. For example, you might have placed a certain credit limit for customers and want to update it in all the records. You would use an Update query.

• Append Query This type of query is used to copy records from one table and place them (append them) into another table. For example, you might want to append employee records from an Active Employee table to a Former Employee table.

• Delete Query This type of query is used to delete records from a table. For example, you might want to delete old records from a table based on particular criteria. Again, you could create a select query using the Wizard (the select query pulls the information together) and then change the query to a delete query in the Design view. The delete query would select the same data designated in the query, but it would delete it from the specified table or tables.

Now, you might be thinking that all these query types are a little too much to handle. However, you create different query types just as you would a select query. As a matter of fact, you actually design each of these different query types as a select table (using a wizard, Query Design view, or a combination of both), and then you change the query type in the Query Design view. It's just a matter of selecting the query type from the Query menu.

For example, Figure 15.6 shows a select query that was created with the wizard and then opened in the Query Design view. The Query was then changed to a delete query using the Query menu (as shown in the figure). The purpose of this delete query was to remove customers from the Customers table that resided in Minnesota (MN). Note that under the State field in the query grid, the parameter "MN" was added to the criteria line (because MN is the information that will be used to delete certain records).

Figure 15.6. Special query types such as delete queries can be quickly created from select queries.

When you run this query (by selecting Query , then Run), any customer in the Customers table who is from Minnesota will be removed from the table. We will be working with the Query grid and the Query Design view commands in the next lesson. So, keep in mind that the special queries that we have discussed in this section are really just modified select queries.