Microsoft KB Archive/214027

= XL2000: Error Message: Query Can Have Only One Outer Join =

Article ID: 214027

Article Last Modified on 10/8/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Query 2000

-



This article was previously published under Q214027





SYMPTOMS
In Microsoft Query, if you attempt to create more than one outer join, you will receive the following error message:

Query can have only one outer join.



CAUSE
Microsoft Query is limited to one outer join between two tables.

NOTE: Microsoft Query can perform several inner joins and/or a single outer join in a query.



WORKAROUND
To work around this limit, you can create multiple outer joins by modifying the SQL statement containing the single outer join so that it performs multiple outer joins.

After you make the modification, Microsoft Query will not be able to graphically display the multiple outer joins, but it will be able to display the results from the query in the Data pane.

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.

The following is an example of a double outer join using two related dBASE tables. For the example to work correctly, you must have a data source installed for the dBASE file format.

Creating Sample Databases
  In Microsoft Excel, create the following table:      A1: FIRSTNAME     B1: LASTNAME       C1: LETTER A2: John         B2: Smith          C2: a      A3: Sam           B3: Smith          C3: b      A4: Mike          B4: Smith          C4: c      A5: Jack          B5: Johnson        C5: d      A6: John          B6: Johnson        C6: e      A7: Sam           B7: Johnson        C7: f      A8: Mike          B8: Johnson        C8: g                     On the File menu, click Save As. Save the file in the dBASE file format, and name it EMPA.dbf .  On a new worksheet, create another table with the following information:      A1: FIRSTNAME     B1: LASTNAME       C1: COUNT A2: Sam          B2: Johnson        C2: 6 A3: Mike         B3: Johnson        C3: 7 A4: George       B4: Smith          C4: 1 A5: Sam          B5: Smith          C5: 2 A6: Mike         B6: Smith          C6: 3  On the File menu, click Save As. Save the file in the dBASE file format, and name the file EMPB.dbf .</ol>

Creating Two Outer Joins
<ol> Close any open Excel Workbooks, and then start a new Excel Workbook.</li> On the Data menu, point to Get External Data and click New Database Query.</li> In the Choose Data Source dialog box, click dBASE files, and then click OK.</li> In the Query Wizard - Choose Columns dialog box, click to select empa and click the > button to add all of its columns to the Columns in your query box. Click empb and click the > button to add all of its columns to the Columns in your query box. Click Next. When the Microsoft Query error message appears, click OK.</li> On the Table menu, click Joins. In the Joins dialog box, click empa.FIRSTNAME in the Left list. Click = in the Operator list, and click empb.FIRSTNAME in the Right list. Click 2 in the Join Includes group to specify a LEFT OUTER join. Click Add, and then click Close.</li> Click the View SQL button.

The following SQL statement will be displayed:

SELECT empa.FIRSTNAME, empa.LASTNAME, empa.LETTER, empb.FIRSTNAME, empb.LASTNAME, empb.COUNT FROM {oj empa empa LEFT OUTER JOIN empb empb ON empa.FIRSTNAME = empb.FIRSTNAME}

</li> Modify the SQL statement by adding and empa.LASTNAME=empb.LASTNAME to the end of the statement so that it matches the following statement:

SELECT empa.FIRSTNAME, empa.LASTNAME, empa.LETTER, empb.FIRSTNAME, empb.LASTNAME, empb.COUNT FROM {oj empa empa LEFT OUTER JOIN empb empb ON empa.FIRSTNAME = empb.FIRSTNAME and empa.LASTNAME=empb.LASTNAME}

</li> In the SQL dialog box, click OK.</li> When you get the following message, click OK:

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

</li></ol>

You will then have your double outer join. The results are depicted below: <pre class="fixed_text">  FIRSTNAME  LASTNAME   LETTER   FIRSTNAME  LASTNAME  COUNT John      Smith       a   Sam        Smith       b        Sam        Smith     2 Mike      Smith       c        Mike       Smith     3 Jack      Johnson     d   John       Johnson     e   Sam        Johnson     f        Sam        Johnson   6 Mike      Johnson     g        Mike       Johnson   7

<div class="moreinformation_section">

MORE INFORMATION
Joins are used in Microsoft Query to associate tables using common fields. In a two-table example, an outer join retrieves all the records from one table and only those records from the other table for which values in the joined fields are equal.

Keywords: kbprb KB214027

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.