Subqueries

Subqueries

Sometimes it may not be possible to retrieve the results that you need from a simple Select statement. At times, you might need to create a Select statement and compare the results to that of another statement. In that case, you would want to use subqueries. A subquery is a query that is nested inside another query. There are two types of subqueries that you can use:

• The In Operator

• The Embedded Select Statement

The In Operator

The In operator is used in a Select statement primarily to specify a list of values to be used with a primary query. A classic example is if you wanted to find all your customers who lived in California. You could write a Select statement using the In operator to accomplish that:

Select * From Customers Where CustomerState In ("Ca")

This statement would effectively return all the customers who live in the state of California, assuming you had a field for state.

The Embedded Select Statement

An Embedded Select statement is used when you want to perform a query within the where clause of a primary query. Suppose you wanted to see a list of customers who have completed orders for the week.

Select * From Customers Where CustomerID In 
(Select Distinct CustomerID From Orders)

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

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:

1. Select the Query tab from the objects column.

2. Select Create Query in design view.

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

   

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

5. After your query has been established, select SQL View from the View menu.

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

   

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

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.

Select SQL View from the View menu. Figure 27.8 shows the query that is generated.

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.