Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Everything You Need to Know About Query Basics

Creating a basic query is easy because Microsoft has given us a user-friendly, drag-and-drop interface. There are two ways to start a new query in Access 2003. The first way is to select the Queries icon from the Objects list in the Database window; then double-click the Create Query in Design View icon or the Create Query by Using Wizard icon (see Figure 4.1). The second method is to select the Queries icon from the Objects list in the Database window and then click the New command button on the Database window toolbar. The New Query dialog appears (see Figure 4.2). This dialog box lets you select whether you want to build the query from scratch or use one of the wizards to help you. The Simple Query Wizard walks you through the steps for creating a basic query. The other wizards help you create three specific types of queries: Crosstab, Find Duplicates, or Find Unmatched.

Adding Tables to Your Query

If you select Design view rather than one of the wizards, the Show Table dialog box appears (see Figure 4.3). Here, you can select the tables or queries that supply data to your query. Access doesn't care whether you select tables or queries as the foundation for your queries. You can select them by double-clicking on the name of the table or query you want to add or by clicking on the table and then clicking Add. You can select multiple tables or queries by using the Shift key to select a contiguous range of tables or the Ctrl key to select noncontiguous tables. When you have selected the tables or queries you want, click Add and then click Close. This brings you to the Query Design window shown in Figure 4.4.

TIP

An alternative way to add a table is to first select Tables from the Objects list in the Database window. Then select the table on which you want the query to be based. With the table selected, select New Query from the New Object drop-down list on the toolbar or choose Query from the Insert menu. The New Query dialog appears. This is an efficient method of starting a new query based on only one table because the Show Table dialog box never appears.

Adding Fields to Your Query

You're now ready to select the fields you want to include in the query. The query shown in Figure 4.4 is based on the tblClients table included in the CHAP4.MDB database on the sample code CD-ROM. Notice that the query window is divided into two sections. The top half of the window shows the tables or queries that underlie the query you're designing; the bottom half shows any fields that will be included in the query output. You can add a field to the query design grid on the bottom half of the query window in several ways:

• Double-click the name of the field you want to add.

• Click and drag a single field from the table in the top half of the query window to the query design grid below.

• Select multiple fields at the same time by using your Shift key (for a contiguous range of fields) or your Ctrl key (for a noncontiguous range). You can double-click the title bar of the field list to select all fields; then click and drag any one of the selected fields to the query design grid.

TIP

You can double-click the asterisk to include all fields within the table in the query result. Although this is very handy, in that changes to the table structure magically affect the query's output, I believe that this "trick" is dangerous. When the asterisk is selected, all table fields are included in the query result regardless of whether they are needed. This can cause major performance problems in a LAN, WAN, or client/server application.

Open the Northwind database that comes with Access. (This database is not installed unless you designate that you want to install sample files when you install Access.) If you want to prevent the Startup form from appearing, hold down your Shift key as you open the database. Click the Query icon and then click New. Select Design view from the New Query dialog. Add the Customers table to the query. Follow these steps to select six fields from the Customers table:

Click and drag any of the selected fields from the table on the top half of the query window to the query design grid on the bottom. All six fields should appear in the query design grid. You might need to use the horizontal scrollbar to view some of the fields on the right.Chapter 11). Clicking Run is preferable because you don't have to worry about what type of query you're running. After running a Select query, you should see what looks like a datasheet, with only the fields you selected. To return to the query's design, click the Query View button.

TIP

Introduced with Access 2002 are shortcut keys that allow you to easily toggle between the various query views: Ctrl+>, Ctrl+Period, Ctrl+<, and Ctrl+Comma. Ctrl+> and Ctrl+Period take you to the next view; Ctrl+< and Ctrl+Comma take you to the previous view.

Removing a Field from the Query Design Grid

To remove a field from the query design grid, follow these steps:

Assume that you have decided to remove the Region field from the query design grid. Use the horizontal scrollbar to see the Region field on the query design grid, and then do the following:

Inserting a Field After the Query Is Built

The process for inserting a field after a query is built differs, depending on where you want to insert the new field. If you want to insert it after the existing fields, it's easiest to double-click the name of the field you want to add. If you prefer to insert the new field between two existing fields, it's best to click and drag the field you want to add, dropping it onto the field you want to appear to the right of the inserted field.

To insert the Country field between the ContactTitle and Phone fields, click and drag the Country field from the table until it's on top of the Phone field. This inserts the field in the correct place. To run the query, click Run on the toolbar.

Moving a Field to a Different Location on the Query Design Grid

Although the user can move a column while in a query's Datasheet view, sometimes you want to permanently alter the position of a field in the query output. You can do this as a convenience to the user or, more importantly, because you will use the query as a foundation for forms and reports. The order of the fields in the query becomes the default order of the fields on any forms and reports you build using any of the wizards. You can save yourself quite a bit of time by ordering your queries effectively.

To move a single column, follow these steps:

Follow these steps to move more than one column at a time:

Move the ContactName and ContactTitle fields so that they appear before the CompanyName field. Do this by clicking and dragging from ContactName's column selector to ContactTitle's column selector. Both columns should be selected. Click again on the column selector for either column, and then click and drag until the thick black line jumps to the left of the CompanyName field.

NOTE

Moving a column in the Datasheet view doesn't modify the query's underlying design. If you move a column in Datasheet view, subsequent reordering in the Design view isn't reflected in the Datasheet view. In other words, Design view and Datasheet view are no longer synchronized, and you must reorder both by hand. This actually serves as an advantage in most cases. As you will learn later, if you want to sort by the Country field and then by the CompanyName field, the Country field must appear to the left of the CompanyName field in the design of the query. If you want the CompanyName to appear to the left of the Country in the query's result, you must make that change in Datasheet view. The fact that Access maintains the order of the columns separately in both views allows you to easily accomplish both objectives.

Saving and Naming Your Query

To save your query at any time, click the Save button on the toolbar. If the query is a new one, Access prompts you to name your query. Query names should begin with the tag

qry so that you can easily recognize and identify them as queries. It's important to understand that, when you save a query, you're saving only the query's definition, not the actual query result.

Return to the design of the query. To save your work, click Save on the toolbar. When prompted for a name, call the query qryCustomers.