Join Expression not supported!!

[Mighty]

The following section of a sql command is my table join. This gives me a "Join Expression not supported" error. Can anyone tell me why or suggest a way around it.....please.


FROM ([Invoice Master] INNER JOIN ([Invoice Detail] INNER JOIN [Part Master] ON [Invoice Detail].PRTNUM_32 = [Part Master].PRTNUM_01) ON [Invoice Master].INVCE_31 = [Invoice Detail].INVCE_32) LEFT JOIN ([Company Master] INNER JOIN [Country List] ON [Company Master].Country = [Country List].Country) ON [Invoice Master].CUSTID_31 = [Company Master].maxID


Mighty

 

[Crowley16]

I usually use the querybuilder to sort out my joins, and then edit the resulting sql to fit needs...

--------------------
Procrastinate Now!

 

[rac2]

FROM ([Invoice Master]
       INNER JOIN ([Invoice Detail]
                   INNER JOIN [Part Master] ON [Invoice Detail].PRTNUM_32 = [Part Master].PRTNUM_01
                  ) ON [Invoice Master].INVCE_31 = [Invoice Detail].INVCE_32
     )
LEFT JOIN ([Company Master]
           INNER JOIN [Country List] ON [Company Master].Country = [Country List].Country
          ) ON [Invoice Master].CUSTID_31 = [Company Master].maxID

I dont know, it seems like the syntax is simply wrong.

FROM [Invoice Master]
INNER JOIN [Invoice Detail]
   ON [Invoice Master].INVCE_31 = [Invoice Detail].INVCE_32
INNER JOIN [Part Master]
   ON [Invoice Detail].PRTNUM_32 = [Part Master].PRTNUM_01
LEFT JOIN ([Company Master]
   ON [Invoice Master].CUSTID_31 = [Company Master].maxID
INNER JOIN [Country List]
   ON [Company Master].Country = [Country List].Country

The Query Design view is such a good feature of Access, why not use it.

 

[PHV]

I don't think you can do an inner join inside a left join.
Try to save a query joining [Company Master] and [Country List] and then use a left join to this query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Mighty]

In response to Rac2's comments, I used the query design view of access and that it the code that it gave me. However, I had already modified it to what you had suggested and it is working.

Thanks for the suggestions.

Mighty