Using the Query Design Window

The Simple Query Wizard has limited usefulness, so the better approach is to design your queries from scratch in Access's graphical Query Design window. The Query Design window is one of Access's most powerful features.

To devise a simple query that lets you customize mailing lists for selected customers of Northwind Traders, for example, follow these steps:

1. Double-click the Create Query in Design View shortcut to open the Query Design window. The Show Table dialog is superimposed on the Query Design window, as shown in Figure 9.10. The tabbed lists in the Show Table dialog let you select from all existing tables, all queries, or a combination of all tables and queries. You can base a new query on one or more previously entered tables or queries.

   Figure 9.10. When you open a new query in Design view, the Show Table dialog lets you select from lists of tables, queries, or both to designate the new query's data source.

   

   Note

   

   If you select a table in the Tables page of the Database window, and then click the New Object toolbar button and select Query from the drop-down menu, Access automatically places the selected table in the Query Design window, without displaying the Show Tables dialog.

2. This example uses only tables in the query, so accept the default selection of Tables. Click (or use the key to select) Customers in the Show Table list to select the Customers table and then click the Add button. Alternatively, double-click Customers to add the table to the query. You can use more than one table in a query by choosing another related table from the list and choosing Add again. This example, however, uses only one table. After selecting the tables that you want to use, click Close.

The Fields list for the Customers table appears at the left in the upper pane of the Query Design window, and a blank Query Design grid appears in the lower pane. The Fields list displays all the names of the fields of the Customers table, but you must scroll to display more than five entries with the default Fields list size. The asterisk (*) item at the top of the list is a shortcut symbol for adding all table fields to the query.

Selecting Fields for Your Query

After you add a table from the Show Table dialog, the next step is to decide which of the table's fields to include in your query. Because you plan to use this query to create a customer mailing list, you must include the fields that make up a personalized mailing address.

To select the fields to include in the Query Design grid, do this:

1. When you open the Query Design window, the cursor is located in the Field row of the first column. Click the List Box button that appears in the right corner of the first column or press Alt+ to open the Field Names list (see Figure 9.11).

   Figure 9.11. One way to add a field to your query is to select it in the drop-down query fields list of the Query Design grid.

   

2. Select the ContactName field as the first field header of the query or use the key to highlight the name and press Enter. The Field list in the lower pane closes.

3. Move the cursor to the second column by using the or Tab key. Double-click CompanyName in the Customers Field list in the upper pane to add CompanyName as the second field of your query. Double-clicking entries in the upper pane's list is the second method that Access provides to add fields to a query.

4. Access offers a third method of adding fields to your query: the drag-and-drop method. To use the drag-and-drop method to add the Address, City, Region, PostalCode, and Country fields to columns 3 through 7, first select the fields. In the Customers Field list of the upper pane's Query Design window, click Address, and then Shift+click Country. Alternatively, select Address with the key, hold the Shift or Ctrl key, and press the key four more times. You've selected the Address, City, and Region fields, as shown in the Customers field list of Figure 9.12.

   Figure 9.12. You also can select multiple fields in the table fields list and drag them to the Fields row of the Query Design grid.

   

5. Position the mouse pointer over the selected fields and click the left mouse button. Your mouse pointer turns into a symbol representing the three selected field names (after you drag the mouse a bit). Drag the symbol for the three fields to the third column of your query's Field row, as shown in Figure 9.12, and release the left mouse button.

   Note

   Access adds the five fields to your query, in sequence, starting with the column in which you drop the symbol. When the mouse pointer is in an area where you can't drop the fields, it becomes the international Do Not Enter symbol shown in the upper pane of the Query Design window of Figure 9.12.

   The Query Design grid in the lower pane displays four columns (in the default width) in a normal Query Design window. This query uses seven fields, so you need to drag the edges of the Query Design window to increase the width of the grid's display to expose two additional empty fields.

6. To reduce the columns' width, drag the divider of the grid's header bars to the left. Click the scroll-right button (on the horizontal scroll bar at the bottom of the window) or drag the scroll bar slider button to the right to expose the remaining fields. Your Query Design window appears as shown in Figure 9.13.

   Figure 9.13. Reduce the width of the columns so Design view displays all columns in your current display resolution (800x600 in this book).

   

7. Click the Datasheet View toolbar button to execute the query. Alternatively, click the Run toolbar button to run your query against the Customers table.

You haven't yet entered any selection criteria in the Criteria row of the Query Design grid, so your query result set in the Customers table displays all records. These records appear in the order of the primary key index on the CustomerID field because you haven't specified a sorting order in the Sort row of the Query Design grid. (The values in the CustomerID field are alphabetic codes derived from the Company Name field.) Figure 9.14 shows the result of your first query after adjusting the width of the fields.

Figure 9.14. The initial query design in Datasheet view displays the seven selected fields of all records in the Customers table.

Selecting Records by Criteria and Sorting the Display

The mailing for which you're creating a list with your sample query is to be sent to U.S. customers only, so you want to include in your query only those records that have USA in the Country field. Selecting records based on the values of fields that is, establishing the criteria for the records to be returned (displayed) by the query is the heart of the query process.

Take the following steps to establish criteria for selecting the records to make up your mailing list:

1. Click the Design View toolbar button.

2. To restrict the result of your query to firms in the United States, type USA in the Criteria row of the Country column. Entering a criterion's value without preceding the value with an operator indicates that the value of the field must match the value you type. You don't need to add quotation marks to the expression; Access adds them for you (see the Country column in Figure 9.15).

   Figure 9.15. Add the USA criterion, hide the Country column, and apply an ascending sort order to the PostalCode column to complete the mailing list query.

   

3. Click the Show check box in the Country column to clear the check mark that appeared when you added the column. After you deactivate the Show check box, the Country field doesn't appear when you run your query. If you don't deactivate a Show check box, that field in the query appears in the query's result by default.

4. Move the cursor to the Postal Code column's Sort row and press Alt+ to display the sorting options for that field: Ascending, Descending, and (Not Sorted). Select the Ascending option to sort the query by Postal Code from low codes to high. At this point, the Query Design grid appears as shown in Figure 9.15.

5. Click the Datasheet View or Run button on the toolbar to display the result of the criterion and sorting order.

Figure 9.16 shows the query result set that Jet refers to as an updatable Recordset (also called a Jet Dynaset), which is indicated by the tentative append (*) in the last (empty) row of the query result set. A Recordset object is a temporary table stored in your computer's memory; it's not a permanent component of the database file. You can edit the data in any visible fields of the underlying table(s) in Query Datasheet view if your Recordset is updatable.

Figure 9.16. Datasheet view displays the mailing list query's updatable result set (Recordset).

If you encounter problems with updating query result sets, see the "Missing Required Fields" topic of the "Troubleshooting" section near the end of the chapter.

Jet SQL

The SQL statement for a sorted SELECT query with a criterion is much simpler than the statement of the preceding example:

     SELECT Customers.ContactName, Customers.CompanyName,      Customers.Address, Customers.City, Customers.Region,      Customers.PostalCode     FROM Customers     WHERE (((Customers.Country)="USA"))     ORDER BY Customers.PostalCode; 

The WHERE clause specifies the selection criterion and the ORDER BY clause determines the sort order. ASC[ENDING] is the default sort order; DESC[ENDING] performs a reverse sort. SQL-92 permits abbreviation of the directional keywords. Access adds multiple sets of unneeded parentheses to the WHERE clause; if you remove them all, the query executes correctly.

Preventing Updates to the Query Result Set

You can edit any of the values in the seven columns of the query and, theoretically, add new records because the tentative append record appears in the last row of the query and the new record navigation button is enabled. You can't add a new record, however, because the query doesn't include the primary key field (CustomerID). If you attempt to add a new record, you receive the message shown in Figure 9.17. You must press Esc to delete all characters you typed in any field of the tentative append record.

Figure 9.17. If you attempt to add a new record to the query, you receive the message shown here because you can't add a value for the primary key field, CustomerID.

The message that appears to a user of your query who attempts to add a new record is confusing, because most database users don't know what "primary key" or "null" value means. It's good database design practice to prevent users from attempting operations they can't complete, so you should designate the query as not updatable by changing the query type to a Jet Snapshot. The terms Dynaset and Snapshot refer to the cursor type of the query; a Dynaset has an updatable cursor and a Snapshot has a read-only cursor. A cursor is what RDBMSs use to navigate, read, and if the cursor is updatable update the rows returned by the query and add a new row.

To change the type of cursor, which doesn't affect the SQL statement for the query, do this:

1. Click the Design View button on the toolbar to return to Query Design view.

2. Right-click in an empty area in the upper Query Design pane, and choose Properties to open the Query Properties dialog.

3. Select the Recordset Type text box, open the drop-down list, and choose Snapshot (see Figure 9.18).

   Figure 9.18. Open the Query Properties dialog and select Snapshot as the value of the Recordset Type property to create a read-only query result set.

   

4. Return to Datasheet view to verify that the tentative append record is missing and the new record navigation button is disabled (see Figure 9.19).

   Figure 9.19. The read-only Snapshot query doesn't have a tentative append record and the new record button is disabled.

   

5. Press Ctrl+S, choose File, Save, or close the query, type a name for the query in the text box, qryCustomersUSA for this example, and click OK to save the query.

When you save the query, the Northwind.mdb file saves only the design specifications of the query, not the values that the query contains. The query design specification is called a QueryDef object. When you open the query, Jet executes the query and displays the result set in Datasheet view.

Creating More Complex Criteria

To limit your mailing to customers in a particular state or group of states, you can add a Criteria expression to the Region or PostalCode field. To restrict the mailing to customers in California, Oregon, and Washington, for example, you can specify that the value of the PostalCode field must be equal to or greater than 90000. Alternatively, you can specify that Region values must be CA, OR, and WA.

Follow these steps to restrict your mailing to customers in California, Oregon, and Washington:

1. Open the query, and click the Design View toolbar button.

2. Move to the Region column and type CA in the first criterion row of the Region column. Access adds the quotation marks around CA (as it did when you restricted your mailing to U.S. locations with the USA criterion).

3. Press the key tzo move to the next criterion row in the Region column. Type OR and then move to the third criterion row and type WA. Your query design now appears as shown in Figure 9.20. Access also adds the required quotation marks to these criteria.

   Figure 9.20. You can further restrict records returned by the query with additional criteria in the Region field.

   

4. Click the Datasheet View or Run toolbar button. The query result set appears as shown in Figure 9.21.

Figure 9.21. The query result set with the additional criteria include only records for the three West Coast states.

After you type a criterion on the same line as a previously entered criterion in another field, only those records that meet both criteria are selected for display. In the preceding example, therefore, records with Region values equal to CA and Country values equal to USA, and records with Region values of OR and WA are displayed.

Jet SQL

The SQL statement that includes the additional criteria is

[View full width]
    SELECT Customers.ContactName, Customers.CompanyName,      Customers.Address, Customers.City, Customers.Region,      Customers.PostalCode    FROM Customers    WHERE (((Customers.Region)="CA") AND ((Customers. Country)="USA"))     OR (((Customers.Region)="OR")) OR (((Customers.Region)="WA"))  ORDER BY Customers.PostalCode; 

Again, Access adds many superfluous parentheses, but the first element of the WHERE clause requires a single pair in the (Customers.Region ="CA" AND Customers.Country)="USA") expression.

To be displayed, records for Region values OR and W A need not have Country values equal to USA, because the USA criterion is missing from the OR and WA rows. This omission doesn't affect the selection of records in this case, because all OR and W A records also are USA records. Thus the WHERE clause can be simplified to WHERE Customers.Region="CA" OR Customers.Region="OR" OR Customers.Region="WA". If you edit the SQL statement accordingly, the query result set is the same.

Changing the Names of Query Column Headers

You can substitute a query's field header names with column header names of your choice a process called aliasing but only if the header name hasn't been changed by an entry in the field Caption property of the table. If yours is a U.S. firm, for example, you might want to change Region to State and PostalCode to ZIP. (Canadian firms might want to change only Region to Province.)

As demonstrated in the following example, you can't change the PostalCode field for queries based on the Customers table because the PostalCode field previously has been changed (aliased) to Postal Code by the Caption property for the field. You can, however, make the change to the Region field because this field isn't aliased at the table level.

Tip

If you already have a main document for mail merge operation, substitute the main merge document's merge field names for the table's field header names in your query.

For more information on merging data with documents, see "Using the Access Mail Merge Wizard," p. 308.

Note

Field names in queries that have been altered by use of the Caption property in the source table can't be aliased, so don't use the Caption property of table fields. If you want to display different field headers, use a query for this purpose. In a client/server RDBMS, such a query is called an SQL VIEW. Aliasing field names in tables rather than in queries isn't considered a generally accepted database design practice.

To change the query column header names, perform the following steps:

1. Click the Design View button on the toolbar. Then place the cursor in the Field column containing the field header name that you want to change in this case, the Region column.

2. Press F2 to deselect the field; then press Home to move the cursor to the first character position.

3. Type the new name for the column and follow the name with a colon (with no spaces) as in State:. The colon separates the new column name that you type from the existing table field name, which shifts to the right to make room for your addition. The result, in this example, is State: Region.

4. Use the arrow key to move to the PostalCode field and repeat steps 2 and 3, typing ZIP: as that header's new name. The result is ZIP: PostalCode.

5. Change the column header for the ContactName field to Contact; change the column header for the CompanyName field to Company.

6. Delete the three criteria ("CA" Or "OR" Or "WA" criterion you added to the SQL statement described in the preceding section) from the State: Region column so that all records for the United States appear (see Figure 9.22). If you altered the SQL statement, add the Country field to the grid, clear the Show check box, and add USA as the criterion.

   Figure 9.22. This query design attempts to assign aliases to field names that have Caption property values assigned at the table level.

   

7. Click the Run or Datasheet View button on the toolbar to execute the query. Observe that only the Region column header is changed to State; the other columns are unaffected by the alias entry (see Figure 9.23).

   Figure 9.23. The table's Caption property value overrides alias names assigned by the query. Only the Region field, which doesn't have a caption, is aliased to State in this example.

   

8. Choose File, Save As, and save your query with the name qryUSMailList.

Jet SQL

The SQL statement that attempts to rename all query columns is

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

Although the SQL statement renames four fields, only the Customer.Region field receives a renamed column.

Tip

To make field aliasing in queries operable, in Table Design view delete the entry in the Caption field for each aliased field of the table. Deleting these entries makes the aliases you entered in the preceding example work as expected.

Printing Your Query as a Report

You often use queries to print quick, ad hoc reports. Access 2003 lets you print your report to a printer, a Microsoft Word .rtf (rich-text format) file, an Excel worksheet .xls file, a DOS .txt (text) file, or as an attachment to an e-mail message. You also can publish a query to a Web server. The table export procedures described in the preceding chapter also apply to queries.

Previewing your query table's appearance to see how the table will appear when printed is usually a good idea. After you determine from the preview that everything in the table is correct, you can print the finished query result set in various formats.

To preview a query result set before printing it, follow these steps:

1. In Query Datasheet view, click the Print Preview toolbar button. A miniature version of the query table appears in Report Preview mode.

2. Position the Zoom pointer (the magnifying glass cursor) anywhere on the table and click the left mouse button or the Zoom button above the window to view the report at approximately the scale at which it will print.

3. Use the vertical and horizontal scroll bar buttons to position the preview in the window (see Figure 9.24).

   Figure 9.24. You can quickly print a query by clicking the Print Preview toolbar button and clicking the table to zoom it to approximately the print scale.

   

   Note

   Field width in the query table is based on the column width that you last established in Run mode. You might have to drag the right edge of the field header buttons to the right to increase the columns' width so that the printed report doesn't truncate the data. If the query data's width exceeds the available printing width (the paper width minus the width of the left and right margins), Access prints two or more sheets for each page of the report.

4. Right-click the Print Preview window and choose Page Setup to open the Page Setup dialog shown in Figure 9.25. If necessary, click the Margins tab to display the Margins page.

   Figure 9.25. The Margins page of the Print Setup dialog lets you change printing margins from the default 1-inch values. The Page page has orientation, paper size and source, and printer selection options.

   

5. Enter any changes that you want to make to the margins; mark the Print Headings check box if you want to print the field header names. Click the Page tab to change the print orientation, paper size or source, or printer. Then click OK to return to Print Preview.

6. Click the Print toolbar button to print your query report.