Microsoft KB Archive/96464

{|
 * width="100%"|

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

 * }

Q96464

-

The information in this article applies to:


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

The following is the SQL statement displayed for Query1 when you choose SQL from the View menu:

 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.
 * 1) In the Totals list box in the Customer ID field, select Group By; in the Ship Via field, select Count.
 * 2) Clear the Show check box in the Ship Via field and type &quot;=3&quot; in the Criteria record.
 * 3) Save this query as Query2. Choose Close.

The following is the SQL statement for the second query:

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

When you run Query2, a list of those customers who have used three different shippers is displayed.

Additional query words: sub-query sub-select SQL relational

Keywords : kbusage

Issue type : kbhowto

Technology :