Creating Other Types of Queries

Access lets you create the following four basic types of queries to achieve different objectives:

• Select queries extract data from one or more tables and display the data in tabular form.

• Crosstab queries summarize data from one or more tables in the form of a spreadsheet. Such queries are useful for analyzing data and creating graphs or charts based on the sum of the numeric field values of many records.

• Action queries create new database tables from query tables or make major alterations to a table. Such queries let you add or delete records from a table or make changes to records based on expressions that you enter in a query design.

• Parameter queries repeatedly use a query and make only simple changes to its criteria. The mailing list query that you created earlier is an excellent candidate for a parameter query because you can change the criterion of the Region field for mailings to different groups of customers. When you run a parameter query, Access displays a dialog to prompt you for the new criterion. Parameter queries aren't actually a separate query type because you can add the parameter function to select, crosstab, and action queries.

Chapter 11 and Chapter 13 explain how to create each of the four query types. Creating a table from the mailing list query to export to a mail merge file is an example of an action query. In fact, this is the simplest example of an action query and also the safest because make-table queries don't modify data in existing tables. A make-table query creates a new table from your query result set.

To review the use of tables for Word mail merge operations, see "Using the Access Mail Merge Wizard," p. 308.

Creating and Using a Simple Make-Table Action Query

To create a table from your mailing list query, you first must convert the query from a select to an action query. Follow these steps to make this change:

1. Open your mailing list query in Query Design view and choose Query, Make-Table Query. (You can access the Query menu only in Query Design view.) Alternatively, click the Query Type toolbar button and select Make-Table Query to open the Make Table dialog.

2. In the Table Name text box, type a descriptive table name for your query table, such as tblUSMailList(see Figure 9.26).

   Figure 9.26. Specify the table name for your make-table query. When creating a table with a query, it's a good practice to use the tbl prefix to identify the table as one created by a query.

   

   Note

   The Make Table dialog lets you define your query table's properties further in several ways. You can add the table to the Northwind database by choosing the Current Database option (the default). You also can pick the Another Database option to add the table to a different database that you specify in the File Name text box.

3. Click OK. Access converts your select query to the make-table type of action query.

4. Save your make-table query with a new name and close it. The query's icon in the Database window now is prefixed by an exclamation point, which indicates that the query is an action query.

Jet SQL

The SQL statement for the make-table query is

    SELECT Customers.ContactName AS Contact,      Customers.CompanyName AS Company,      Customers.Address, Customers.City,      Customers.Region AS State, Customers.PostalCode AS ZIP    INTO tblUSMailList    FROM Customers    WHERE (((Customers.Country)="USA"))    ORDER BY Customers.PostalCode; 

The clause that differentiates the make-table query from the select query from which it's derived is INTO tblMailList. The INTO clause lets you specify the table name.

Now that you've converted your query from a select query to an action query, you can create a new U.S. mailing list table. To create the table, follow these steps:

1. Run the newly converted action query table to create your mailing list by double-clicking its name in the Queries page of the Database window. Acknowledge the message that asks you to confirm that the table, if it exists, will be overwritten, and acknowledge another warning about the number of rows to be "pasted" to the new table (see Figure 9.27).

   Figure 9.27. Access displays two warning messages before your make-table query creates the table if you haven't cleared the Action Queries and Record Changes text boxes on the Edit/Find page of the Options dialog.

   

2. Click the Tables Object shortcut in the Database window. Access adds the new tblUSMailList table to the list of tables in the Northwind database.

3. Double-click the tblUSMailList item to open the table. Its contents are identical to the contents of the Datasheet view of the make-table query.

After you create the new table, you can export its data to any of the other file formats supported by Access. To do so, use any of the methods described in Chapter 8, "Linking, Importing, and Exporting Data."

Adding a Parameter to Your Make-Table Query

A simple modification to your mailing list query lets you enter a selection criterion, called a parameter, from a prompt generated by Access. Parameterized queries are very useful for generating custom tables for Word mail-merge operations. You can use the same merge specifications with different tables to generate multiple lists for selected regions or types of recipients.

For more information on parameterized queries, see "Designing Parameter Queries," p. 440.

To create a parameterized SELECT query, follow these steps:

1. Close the tblUSMailList table and then click the Queries shortcut in the Database window.

2. Right-click the qryUSMailList query that you created earlier in the chapter, and choose Design View to display your make-table action query in design mode.

3. Type [Enter the state code:] in the first criterion row of the State: Region column, as shown in Figure 9.28. The enclosing square brackets indicate that the entry is a prompt for a parameter when you run the action query.

   Figure 9.28. Adding a criterion enclosed in square brackets creates a prompt for a value to filter the query result set.

   

4. Choose File, Save As, and save your query with a new name, such as qryStateMailList.

5. Change to Datasheet view. Access opens the Enter Parameter Value dialog, which contains the prompt for you to enter the state criterion. Type WA for this example (see Figure 9.29), and click OK to display the parameter query result set (see Figure 9.30).

Figure 9.29. The Enter Parameter dialog includes the prompt you added as the criterion of the field for which a value is required.

Figure 9.30. This query result set illustrates the effect of applying a parameter to a SELECT query.

Jet SQL

The Jet SQL statement for the parameterized make-table query is

    SELECT Customers.ContactName AS Contact,      Customers.CompanyName AS Company,      Customers.Address, Customers.City,      Customers.Region AS State, Customers.PostalCode AS ZIP     INTO tblUSMailList     FROM Customers     WHERE (((Customers.Region)=[Enter the state code:])      AND ((Customers.Country)="USA"))     ORDER BY Customers.PostalCode; 

The first Customers.Region)=[Enter the state code:] criterion of the WHERE clause specifies the prompt and opens the Enter Parameter Value dialog. This syntax is Jet-specific and isn't supported by SQL-92 or T-SQL. ADP use SQL Server functions to return parameterized query result sets.

Access doesn't limit you to using a single parameter. For example, you can replace the "USA" criterion for the Country field with [Enter the country name:], and drag the Country column to the left of the Region column so the country prompt occurs first. In the case of the Orders table, however, you encounter a problem with countries such as Germany and the UK that don't have entries in Region columns. See the "Problems with Null Values in Parameter Fields" of the next section for more information on this issue.