Microsoft KB Archive/188663

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

Article ID: 188663

Article Last Modified on 9/11/2002

-

APPLIES TO


 * Microsoft Excel 98 for Macintosh
 * Microsoft Query 2000

-



This article was previously published under Q188663



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  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
 On the Table menu, click Joins. In the Left list, click PRODUCT.PRODUCT_ID. In the Right list, click DETAIL.PRODUCT_ID. Select "ALL values from 'PRODUCT' and ONLY records from 'DETAIL' where PRODUCT.PRODUCT_ID = DETAIL.PRODUCT_ID". Click Add. Click Close. 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.</li> On the View menu, click SQL.

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

SSELECT 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}

</li> 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)}

</li> Click OK.</li> 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.</li></ol>

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

<div class="references_section">