| 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 you, there is an easier way. Rather than manually writing all your statements, you could rely on Access to create them for you. If you've been experimenting with Access on your own, you'll notice the Queries tab on the left column. Queries can be constructed and saved for reuse in the future with the query designer. Figure 27.1 shows the query designer. Figure 27.1. The query designer streamlines the way queries are created.
Generating Queries in Design View The easiest and quickest way to generate queries is by creating them in design view. To create a simple select query, follow these steps: -
Select the Query tab from the objects column. -
Select Create Query in design view. -
You can select the tables you want to include in your query from the Show Table dialog box, as shown in Figure 27.2. Select the customers table and click Add. Figure 27.2. The Show Table dialog box enables you to select the tables you want to include in your query.
-
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. Select Customer.* from the Fields drop-down list because this query will select all the records from the customers table. Figure 27.3 shows the drop-down list from the fields table. Figure 27.3. You can select which fields to include in your query.
NOTE If you want to limit your query to two fields rather than the whole table, you can select one field from the fields drop-down list, move over to the right column, and select a different field. -
After your query has been established, select SQL View from the View menu. -
That's it! Figure 27.4 shows how you can copy and paste the SQL statement. Figure 27.4. SQL View presents the SQL code that you can copy and paste into your application code.
-
Save your query with a name that is relevant to what it performs. Figure 27.5 shows the Save As dialog box. Figure 27.5. Save your query by selecting Save As from the File menu. Save your query with a name that is relevant to what the query does.
Generating Queries with Relationships The true power in the Access query designer lies in the fact that it can even generate those complex statements with relationships that everyone hates to write by hand. To create another query that utilizes a relationship: Right-click in the design view of the existing query that you previously created. Figure 27.6 shows the menu that will appear. Select Show Table. Figure 27.6. You can continuously add tables by right-clicking in the designer.
Add the creditcards table. Notice the one-to-many relationship that is maintained between the two tables. In the second column, select CreditCards.*. Figure 27.7 shows the view that you will be seeing. Figure 27.7. Select CreditCards.* to show all credit card fields.
Select SQL View from the View menu. Figure 27.8 shows the query that is generated. Figure 27.8. The query designer streamlines the way queries are created.
Figure 27.9 shows the datasheet view available from the View menu. Notice the query includes all fields from both the customers table and the creditcards table. Figure 27.9. The query designer streamlines the way queries are created.
| 
