Creating a Query with a Wizard | MicrosoftВ® Office ExcelВ® 2003 Step by Step (Step By Step (Microsoft))

The process used to create a simple select query with the Query Wizard is almost identical to that for creating a form with the Form Wizard. With the Query Wizard, you can add one or more fields from existing tables or queries to the new query.

For Access to work effectively with multiple tables, it must understand the relationships between the fields in those tables. You have to create these relationships before using the Query Wizard, by clicking the Relationships button and then dragging a field in one table over the identical field in another table (the field names don t have to be the same in each table, but the field contents must represent the same information).

In this exercise, you will use the Query Wizard to create a new query that combines information from the Customers and Orders tables to provide information about each order. These tables are related through their common CustomerID fields. (This relationship has already been established in the GardenCo database files used in this chapter.)

USE the GardenCo database in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Office 2003 SBS\Queries\QueryWiz folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Microsoft Office System 2003 Step by Step .

OPEN the GardenCo database and acknowledge the safety warning, if necessary.

On the Objects bar, click Queries , and then double-click Create query by using wizard .

The first page of the Simple Query Wizard opens.

Tip	You can also start the Query Wizard by clicking Query on the Insert menu or clicking the arrow to the right of the New Object button list, and then double-clicking Simple Query Wizard.

In the Tables/Queries list, click Table: Orders .
Click the >> button to move all available fields in the Available Fields list to the Selected Fields list.
Select Table: Customers from the Tables/Queries list.
In the Available Fields list, double-click the Address , City , Region , PostalCode , and Country fields to move them to the Selected Fields list, and then click Next .

Tip
If the relationship between two tables hasn t already been established, you will be prompted to define it and then start the wizard again.
Click Next again to accept the default option of showing details in the results of the query.
Change the query title to Orders Qry , leave the Open the query to view information option selected, and then click Finish .

Access runs the query and displays the results in Datasheet view. You can scroll through the results and see that information is displayed for all the orders.
Click the View button to view the query in Design view.

Notice that the Show box is, by default, selected for each of the fields used in the query. If you want to use a field in a query ”for example, to sort on, to set criteria for, or in a calculation ”but don t want to see the field in the results datasheet, you can clear its Show check box.
Clear the Show check box for OrderID , CustomerID , and EmployeeID , and then click the View button to switch back to Datasheet view.

The three fields have been removed from the results datasheet.
Click the View button to return to Design view.

This query returns all records in the Orders table. To have this query match the records for a range of dates, you will convert it to a parameter query, which asks for the date range each time you run it.
In the OrderDate column, click in the Criteria cell , and type the following, exactly as shown:

Between [Type the beginning date:] And [Type the ending date:]
Click the Run button to run the query.
In the dialog box displayed, type 1/1/03 , and press [ENTER].
In the second Enter Parameter Value dialog box, type 1/31/03 , and press [ENTER] again.

The datasheet is displayed again, this time listing only orders between the parameter dates.
Close the datasheet, clicking Yes to save the query.

CLOSE the GardenCo database.