Access Can't Represent the join expression

Question

I built a query in Design view in Access 2007 and now when I try to access the query in design view it gives me the error "Access can't represent the join expression".  I read some information about parentheses possibly being added so I tried to play around with double parenthese (adding, removing) and to no avail.

Any help is greatly appreciated, the query is shown below.

SELECT TMMGR_SHIPMENT.CARRIER_ID, TMMGR_LOAD_STOP.BOL_ID, TMMGR_LOAD_STOP_1.ARRV AS [SHIP DATE], TMMGR_ORD.LATE_DEL, TMMGR_SHIPMENT.FROM_LOC_ID, TMMGR_LOCATION_1.NAME, TMMGR_LOCATION_1.CITY, TMMGR_LOCATION_1.STATE, TMMGR_LOCATION_1.ZIP, TMMGR_SHIPMENT.TO_LOC_ID, TMMGR_LOCATION.NAME, TMMGR_LOCATION.CITY, TMMGR_LOCATION.STATE, TMMGR_LOCATION.ZIP, TMMGR_SHIPMENT_STATUS.STATUS_CODE, TMMGR_SHIPMENT_STATUS.OCCASION_DATE, TMMGR_SHIPMENT_STATUS.APPLICATION_CREATE_DATETIME, TMMGR_SHIPMENT.PCS, TMMGR_SHIPMENT.CUBE, TMMGR_SHIPMENT.WEIGHT

FROM TMMGR_ORD INNER JOIN (TMMGR_ORD_LOAD INNER JOIN (TMMGR_LOAD_STOP AS TMMGR_LOAD_STOP_1 INNER JOIN (((TMMGR_SHIPMENT_STATUS INNER JOIN (TMMGR_SHIPMENT INNER JOIN TMMGR_LOAD_STOP ON (TMMGR_SHIPMENT.ID=TMMGR_LOAD_STOP.LOAD_ID) AND (TMMGR_SHIPMENT.SCHED_NUM=TMMGR_LOAD_STOP.SCHED_NUM)) ON TMMGR_SHIPMENT_STATUS.BOL=TMMGR_LOAD_STOP.BOL_ID) INNER JOIN TMMGR_LOCATION ON TMMGR_SHIPMENT.TO_LOC_ID=TMMGR_LOCATION.ID) INNER JOIN TMMGR_LOCATION AS TMMGR_LOCATION_1 ON TMMGR_SHIPMENT.FROM_LOC_ID=TMMGR_LOCATION_1.ID) ON (TMMGR_SHIPMENT.SCHED_NUM=TMMGR_LOAD_STOP_1.SCHED_NUM) AND (TMMGR_LOAD_STOP_1.LOAD_ID=TMMGR_SHIPMENT.ID)) ON (TMMGR_LOAD_STOP.STOP_NUM=TMMGR_ORD_LOAD.STOP_NUM) AND (TMMGR_ORD_LOAD.SCHED_NUM=TMMGR_LOAD_STOP.SCHED_NUM) AND (TMMGR_ORD_LOAD.LOAD_ID=TMMGR_LOAD_STOP.LOAD_ID) AND (TMMGR_ORD_LOAD.SCHED_NUM=TMMGR_SHIPMENT.SCHED_NUM) AND (TMMGR_ORD_LOAD.LOAD_ID=TMMGR_SHIPMENT.ID)) ON (TMMGR_ORD_LOAD.SPLIT_ID=TMMGR_ORD.SPLIT_ID) AND (TMMGR_ORD_LOAD.SCHED_NUM=TMMGR_ORD.SCHED_NUM) AND (TMMGR_ORD.ID=TMMGR_ORD_LOAD.ORD_ID)

WHERE (((TMMGR_SHIPMENT_STATUS.STATUS_CODE) In ('AB','AG','X1','00')) AND ((TMMGR_SHIPMENT.T_MODE)=2) AND ((TMMGR_LOAD_STOP.STOP_NUM)=2) AND ((TMMGR_LOAD_STOP_1.STOP_NUM)=1));

Answer 1

FROM TMMGR_ORD INNER JOIN (TMMGR_ORD_LOAD INNER JOIN (TMMGR_LOAD_STOP

You have three tables in this line ---

---

Build a little, test a little.

Answer 2

I said I'm only joining on 2 fields specifically between the ord_load and shipment tables.  I know I have way more joins than that.  The Ord_Load and Shipment ARE tables.

Answer 3

I believe I am only joining on 2 fields with ord_load and shipment.  I don't think I have anything additional.

You need to read up on what a join is if you think you only have two.   Count how many equal signs is in the SQL.

          'ord_load' and 'shipment' are not tables or fields.

---

Build a little, test a little.

Answer 4

I saw that article before but it says Access 2003.  Does that hot fix also work for 2007?

I believe I am only joining on 2 fields with ord_load and shipment.  I don't think I have anything additional.

Answer 5

Maybe this KB article would help to troubleshoot: http://support.microsoft.com/kb/902445

Is it really necessary to join TMMGR_ORD_LOAD and TMMGR_SHIPMENT on so many fields? Joining on the PK/FK should suffice.

---

-Tom. Microsoft Access MVP