ACC1x: Using Subquery to Simulate a SQL Sub-SELECT Statement 1.x

• Microsoft Access versions 1.0, 1.1

SUMMARY

This article explains how to use a subquery to simulate a SQL sub-SELECT statement. The procedure below uses the sample database NWIND.MDB to generate a list of customers who have used all three shippers to deliver their orders.

MORE INFORMATION

You either can create a query (called Query1) based on the Customers and Orders tables and another query (called Query2) based on Query1, or you can enter the equivalent SQL statements. This article contains procedures for both methods.

Creating Query1

To create Query1 interactively, use the following steps:

1. In the Database window, create a new query by choosing Query and then choosing New.

2. In the Table/Query box, select the Customers table and choose Add. Select the Orders table and choose Add again.

3. Choose Close. You have now added these two tables to the query. The tables are joined on the Customer ID field.

4. Select the Customer ID field in the Customers table and drag it to the first cell in the Field record in the query grid. Select the Ship Via field from the Orders table and drag it to the second cell in the Field record.

5. Save this query as Query1 and choose Close.

  SELECT  DISTINCT Customers.[Customer ID], Orders.[Ship Via]
  FROM Customers, Orders,
  Customers INNER JOIN Orders ON Customers.[Customer ID] =
  Orders.[Customer ID];

Creating Query2

Create a second query (subquery) based on Query1.

To create Query2 interactively, use the following steps:

1. Create another new query, using step 1 in the previous procedure. Select Query1 as the source of your data.

2. Hold down the CTRL key and select the Customer ID and Ship Via fields from Query1. Drag these fields to the new query grid.

3. Choose the Sum button on the toolbar.

   NOTE: The Sum button looks like the Greek letter Sigma.

4. In the Totals list box in the Customer ID field, select Group By; in the Ship Via field, select Count.

5. Clear the Show check box in the Ship Via field and type "=3" in the Criteria record.

6. Save this query as Query2. Choose Close.

  SELECT  Query1.[Customer ID]
  FROM Query1
  GROUP BY Query1.[Customer ID]
  HAVING (((Count(Query1.[Ship Via]))=3));