Microsoft KB Archive/207868

= ACC2000: "Microsoft Access Can't Represent the Join Expression" Error Message in Query Design =

Article ID: 207868

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q207868





Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



SYMPTOMS
After you create a query with the Simple Query Wizard in Microsoft Access, you may receive the following error message if you try to open the query in Design view:

Microsoft Access can't represent the join expression

([].[] =

[].[]) AND

([].[] =

[].[]) in Design

view.



CAUSE
The Simple Query Wizard adds an extra set of parentheses in the join expression of the SQL statement that it creates for the query.

The error occurs if your query meets both of the following conditions:
 * If your query is based on two or more tables.
 * If two of the tables contain a relationship or join based on a three-field primary key.



RESOLUTION
You can use either of the following methods to work around this problem:  You can create the query in Design view without using the Simple Query Wizard.</li> <li> You can modify the query's SQL statement to remove the extra parentheses. Open the query in Datasheet view, and then click SQL View on the View menu to see the SQL statement. This is an example of a portion of the join expression with the extra parentheses: ON ([tblOne].[Key3] = [tblTwo].[Key3]) AND (([tblOne].[Key1] = [tblTwo].[Key1]) AND ([tblOne].[Key2] = [tblTwo].[Key2])); This is how the expression looks with the extra parentheses removed: ON ([tblOne].[Key3] = [tblTwo].[Key3]) AND ([tblOne].[Key1] = [tblTwo].[Key1]) AND ([tblOne].[Key2] = [tblTwo].[Key2]); </li></ul>

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

<div class="moreinformation_section">

Creating the Tables
<ol> <li>Open the sample database Northwind.mdb.</li> <li> Create the following new table in Design view: <pre class="fixed_text">     Table: tblOne Field Name: Key1 Data Type: Text Field Name: Key2 Data Type: Text Field Name: Key3 Data Type: Text Field Name: Field1 Data Type: Number </li> <li>With the table open in Design view, select fields Key1, Key2, and Key3 by holding down the SHIFT key while you click the row selector button to the left of each field name, and then release the SHIFT key.</li> <li>On the Edit menu, click Primary Key.</li> <li>Save the tblOne table and close it.</li> <li> Create a second new table in Design view: <pre class="fixed_text">     Table: tblTwo Field Name: ID        Data Type: AutoNumber Field Name: Key1 Data Type: Text Field Name: Key2 Data Type: Text Field Name: Key3 Data Type: Text Field Name: Field2 Data Type Text </li> <li>Save the tblTwo table and close it. You do not need to create a primary key.</li></ol>

Creating the Relationships
<ol> <li>On the Tools menu, click Relationships.</li> <li>When the Relationships window opens, click Show Table on the Relationships menu.</li> <li>In the Show Table dialog box, double-click tblOne and then tblTwo. Click Close.</li> <li>In the Relationships window, select fields Key1, Key2, and Key3 in tblOne by holding down the SHIFT key while you click all three fields, and then release the SHIFT key.</li> <li>Drag the selected fields Key1, Key2, and Key3 from the tblOne table, which invokes the Edit Relationships dialog box.</li> <li> Add fields Key1, Key2, and Key3 from the tblTwo table in the Related Table/Query column, so that the Edit Relationships dialog box looks like this: <pre class="fixed_text">     Table/Query: tblOne   Related Table/Query: tblTwo -     Key1                  Key1 Key2                 Key2 Key3                 Key3 </li> <li>Click Create.</li> <li>Close the Relationships window and save the changes.</li></ol>

Creating the Query

 * 1) In the Database window, click Queries under Objects, and then click New.
 * 2) In the New Query dialog box, select Simple Query Wizard, and then click OK.
 * 3) In the Simple Query Wizard dialog box, select tblOne in the Tables/Queries list. Then, add Field1 to the Selected Fields box.
 * 4) Still on the same screen of the Simple Query Wizard dialog box, select tblTwo in the Tables/Queries list. Then, add all of the fields from that table to the Selected Fields box.
 * 5) Click Finish. The Simple Query Wizard constructs the query, and displays the results in Datasheet view. No records are displayed because there is no data in the tables.
 * 6) On the View menu, click Design View. Note that you receive the error message mentioned in the "Symptoms" section.

<div class="references_section">