ACC97: "Microsoft Access Can't Represent the Join Expression" Error Message in Query Design (161861)

RESOLUTION

There are two methods you can use to work around this problem:

You can create the query in Design view without using the Simple Query Wizard.
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]);
						
```

MORE INFORMATION

Steps to Reproduce Problem

Creating the Tables

Open the sample database Northwind.mdb.

Create the following new table in Design view:

      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

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.
On the Edit menu, click Primary Key.
Save the tblOne table and close it.

Create a second new table in Design view:

      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

Save the tblTwo table and close it. You do not need to create a primary key.

Creating the Relationships

On the Tools menu, click Relationships.
When the Relationships window opens, click Show Table on the Relationships menu.
In the Show Table dialog box, double-click tblOne and then tblTwo. Click Close.
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.
Drag the selected fields Key1, Key2, and Key3 from the tblOne table, which invokes the Relationships dialog box.

Add fields Key1, Key2, and Key3 from the tblTwo table in the Related Table/Query column, so the Relationships dialog box looks like this:

      Table/Query: tblOne   Related Table/Query: tblTwo
      -------------------------------------------------
      Key1                  Key1
      Key2                  Key2
      Key3                  Key3

Click Create.
Close the Relationships window and save the changes.

Creating the Query

Click the Query tab in the Database window, and then click New.
In the New Query dialog box, select Simple Query Wizard, and then click OK.
In the "Which fields do you want in your query?" dialog box, select tblOne in the Tables/Queries box. Then, add Field1 to the Selected Fields box.
Still in the "Which fields do you want in your query?" dialog box, select tblTwo in the Tables/Queries box. Then, add all of the fields from that table to the Selected Fields box.
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.
On the View menu, click Design View. Note that the error message appears.

ACC97: "Microsoft Access Can't Represent the Join Expression" Error Message in Query Design (161861)

SYMPTOMS

CAUSE