Create a query using Design view in Access2007

Using the Query Wizard in Access2007 you can quickly create a query, but you cannot be fully qualified for queries, parameter queries, and other complex queries and query wizards that create the criteria you specify. In this case, you can use Design view to create the query directly, or use the Query Wizard to create the query, and then modify it in Design view.

For example, in the database of "teaching management", the students who have excellent results are queried for their "student number", "Name", "Course" and "achievement". Action steps are as follows:

(1) Open the Teaching Management database, on the Create tab, in the other group, click the Query Design button to open the Query Design view window, as shown in Figure 1.

Figure 1 The Query Design view window

(2) In the Show Table dialog box, select the student, course, and results tables, and then click the Add button to add the three tables to the Data Source range pane at the top of the design grid. After they are added, they are automatically displayed as "relationships" between the tables. Finally, click the Close button to close the Show Table dialog box.

(3) in the "Students" table, drag the "Learn Number", "Name" field to the design grid. In the same way, add the course Name field in the course table and the final results field in the results table to the design grid.

(4) In the cell in the Criteria row of the final score column in the design grid, enter the condition ">=90", as shown in Figure 2 in the Design view.

Figure 2 Design view after adding tables, fields, and entering criteria

(5) On the Create tab, in the results group, click the button and open query view to display the results of the query.

(6) on the shortcut Bar, click the "Save" button, open the Save As dialog box, enter the query name "Outstanding students", click the "OK" button.

Tip: In a multiple-table query, you should first establish a relationship between the table and the table. If a relationship is not established, a multiple-table query will create a clutter of excess duplicate records.