Query98: "Query can have only one Outer Join" Error Message

ID: Q188663

The information in this article applies to:

SYMPTOMS

In Microsoft Query 98 Macintosh Edition, when you click Add (in the Joins dialog box) you may receive the following error message:

   Query can have only one outer join

CAUSE

This error message occurs if you already have an outer join and you click Add to add another outer join to your query. Microsoft Query only allows you to create a single outer join through the Microsoft Query interface.

WORKAROUND

You can manually modify the SQL statement for the query to create two outer joins. Please see the "More Information" section of this article for a sample query that contains two outer joins.

MORE INFORMATION

WARNING: Your use or modification of the SQL statement provided in this article is at your own risk. Microsoft provides this SQL statement "as is" without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. Microsoft does not support modifications of the SQL statement to suit specific customer requirements.

Creating the Data Source

1. Start Microsoft Query.

2. On the File menu, click New.

3. Select <New Data Source> and click OK.

4. Type "two outer joins" in the first text box.

5. In the Select A Driver list, click Microsoft 3.01 dBASE PPC.

6. Click Connect.

7. Locate and select the Microsoft Office 98:Sample Files:Sample Databases

   folder. Click "Select 'Sample Databases'".

8. Click OK in the Create New Data Source dialog box.

Creating the Query

1. Click to clear the "Use the Query Wizard to create/edit queries" check

   box.

2. In the Choose Data Source dialog box, click Two Outer Joins. Click OK.

3. Select Detail.dbf and click Open. Select Product.dbf and click Open.

4. Click Cancel.

Creating the Joins

1. On the Table menu, click Joins.

2. In the Left list, click PRODUCT.PRODUCT_ID. In the Right list, click

   DETAIL.PRODUCT_ID.

3. Select "ALL values from 'PRODUCT' and ONLY records from 'DETAIL'
   where PRODUCT.PRODUCT_ID = DETAIL.PRODUCT_ID".

4. Click Add. Click Close.

5. Double-click PRODUCT_ID and QUANTITY in the DETAIL table. Double click

   PRODUCT_ID and IN_STOCK in the PRODUCT table.

   This displays all the records in these four fields that match the
   query created in steps 2-4.

6. On the View menu, click SQL.

   The SQL statement displayed in the SQL dialog box should be:

   SELECT DETAIL.PRODUCT_ID, DETAIL.QUANTITY, PRODUCT.PRODUCT_ID,
   PRODUCT.IN_STOCK FROM {oj 'hd:Microsoft Office 98:Sample Files:
   Sample Databases':'PRODUCT.DBF' PRODUCT LEFT OUTER JOIN 'hd:
   Microsoft Office 98:Sample Files:Sample Databases':'DETAIL.DBF' DETAIL
   ON PRODUCT.PRODUCT_ID = DETAIL.PRODUCT_ID}

7. Change the SQL statement to the following:

   SELECT DETAIL.PRODUCT_ID, DETAIL.QUANTITY, PRODUCT.PRODUCT_ID,
   PRODUCT.IN_STOCK FROM {oj 'hd:Microsoft Office 98:Sample Files:
   Sample Databases':'PRODUCT.DBF' PRODUCT LEFT OUTER JOIN 'hd:
   Microsoft Office 98:Sample Files:Sample Databases':'DETAIL.DBF' DETAIL
   ON (DETAIL.QUANTITY <= PRODUCT.IN_STOCK and PRODUCT.PRODUCT_ID =
   DETAIL.PRODUCT_ID)}

8. Click OK.

9. Click OK when you see the following message:

      SQL Query can't be represented graphically. Continue anyway?

   None of the records displayed have a value in the QUANTITY field that is
   greater than the corresponding value in the IN_STOCK field.

NOTE: The Table pane is no longer displayed because Microsoft Query cannot graphically represent the query with two outer joins that you created.

REFERENCES

For more information about Outer Joins, click Contents And Index on the Help menu in Microsoft Query (or on the Balloon Help menu if you are using a version of the Macintosh operating system earlier than 8.0), click the Index button in Microsoft Query Help, type the following text

   outer joins

and then click Show Topics. Select the "Join tables in a query" topic, and click Go To. If you are unable to find the information you need, ask the Office Assistant.

Additional query words: XL98

Keywords          : kbdta xlquery xlmac 
Version           : MACINTOSH:98
Platform          : MACINTOSH
Issue type        : kbprb

Last Reviewed: July 6, 1998