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.

NOTE

Unfortunately, MSDE and Web Data Administrator don't offer a way to visually create queries. However, if your organization is using SQL Server, chances are you have access to Enterprise Manager. Enterprise Manager exposes a View editor that allows you to construct queries similarly to Access's Query Editor.

NOTE

Out of the box, MySQL doesn't offer a tool to visually create complex SQL queries. However, numerous third-party tools exist that function similarly to Access's Query Editor. One tool that I'm partial to is MySQL Maestro. Although it isn't free, a 30-day trial is available. More on this tool later in this chapter.

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. NOTE If you want to limit your query to two fields rather than the whole table, you can select one field from the Field drop-down list, move over to the right column, and select a different field.
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.

You're now free to save your query for use later. Save your query with a name that is relevant to what it performs by choosing File, Save As.

TIP

So far, you've learned how to write queries on your own. But how does Dreamweaver use those queries to extract data from the database? For the most part, you can construct your queries in Access, copy the SQL code, and then paste it into the Recordset dialog (covered in the next chapter). Even better, Dreamweaver allows you to use saved queries directly from Dreamweaver. For instance, if you wanted to create a complex join, you could create the statement in Access, save the query, and then use the query from Dreamweaver. More on this in the next chapter.

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.
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.

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.