لطفا منتظر باشید ...
Using Access to Generate Queries
Queries lay the foundation for data extraction from databases. You have seen how to create simple and complex queries by hand and you now have a firm grasp as to how they are constructed with clauses, operators, conditions, and expressions. Unfortunately, like most programming and authoring languages, perfection takes time and practice. Fortunately for Access users, there is an easier way.Rather than manually writing all your SQL statements, you can rely on Access to create them for you. If you've been experimenting with Access on your own, you've noticed the Queries tab on the left column. Queries can be constructed and saved for reuse in the future by using the Query Designer (see Figure 24.1).
Figure 24.1. The Query Designer streamlines the way queries are created in Access.
Generating Queries in Design View
The easiest and quickest way to generate queries in Access is by creating them in Design view. To create a simple SELECT query, follow these steps:
| 1. | Select the Queries tab from the objects column. |
| 2. | Double-click the Create Query in Design View option. |
| 3. | Select the tables you want to include in your query from the Show Table dialog, as shown in Figure 24.2. For this example, select the Employees table and click Add.Figure 24.2. The Show Table dialog enables you to select the tables you want to include in your query. |
| 4. | Click Close to close the Show Table dialog. |
| 5. | After your table is added to the designer, you are free to select the fields to include in the statement, the table those fields reside in, how to sort the records, and various criteria to include. For this example, select Employees.* from the Field drop-down list as shown in Figure 24.3.Figure 24.3. You can select which fields to include in your query. |
| 6. | After your query has been established, select SQL View from the View menu. |
That's it! Figure 24.4 shows the resulting query in the SQL View window.
Figure 24.4. SQL View presents the SQL code you can copy and paste into your application code.
Generating Queries with Relationships
The true power in the Access Query Editor lies in the fact that it can generate those complex statements with relationships that everyone hates to write by hand. To create a query that use a relationship, follow these steps:
| 1. | Right-click in the Design view of the query you created in the preceding section. Select Show Table from the context menu. |
| 2. | From the Show Table dialog that opens, select the CreditCards table and click Add. Notice the one-to-many relationship that is maintained between the two tables. Click Close to close the Show Table dialog. |
| 3. | In the second column, select CreditCards.*. Figure 24.5 shows the view you should be seeing.Figure 24.5. Select CreditCards.* to show all credit card fields.[View full size image]  |
| 4. | Select View, SQL View. Figure 24.6 shows the query that is generated, complete with the INNER JOIN.Figure 24.6. Access displays the complex INNER JOIN in its SQL View.[View full size image]  |
Figure 24.7 shows the datasheet view available from the View menu. Notice that the query includes all fields from both the Employees and the CreditCards tables.
Figure 24.7. Access displays the results of joining two tables.
[View full size image]
![[View full size image]](/image/library/english/13881_24fig05_alt.jpg){kind=link}
![[View full size image]](/image/library/english/13881_24fig06_alt.jpg){kind=link}