Using Join in a Distributed Query !!!!!!

Hi all

I am currently Migrating alot of Access reports to SQL..
I have a linked server in SQL created to an IBM Database B2
So I am converting the SQL created with access into Distributed queries in SQL
All has gone well until I have a join of 2 tables that I need to do from WITHIN a Distributed Query.



Eg: The following works perfectly

Linked server is Movex_MVXAMODSFC
PIMEIG is a table within the linked server


SELECT Z2CONO AS 'Company', Z2WZIS AS 'TAC', Z2TX40 AS 'Desc'
FROM OPENQUERY(MOVEX_MVXAMODSFC, 'Select * from MVXAMODSFC.PIMEIG ')WHERE Z2CONO=200

The statemt above Returns ok and I get

Company Tac Desc
DataDataData
DataDataData


But if I try this below....

Linked Server Movex_MVXAMODSFC
MITMAS is a table within the linked server
MITBAL is a Table within the linked server

I am trying to do a join on these 2 tables where
The field MITMAS.MMCONO = MITBAL.MBCONO and MITMAS.MMITNO = MITBAL.MBITNO


I'm not sure how to go about this on a distributed query as
you only get to do 1 SELECT Stament From an OPENQUERY as above.

Does Anyone know how to do this ??

I have tried the folowing but I know the overall approach is incorrect.


SELECT MMSTAT AS Status, MMITNO AS [Item Number], Sum(MITBAL.MBSTQT) AS [In Stock], Sum(MITBAL.MBAVAL) AS [Allocable on hand Balance]
FROM OPENQUERY(MOVEX_MVXAMODSFC MVXADTA_MITMAS LEFT JOIN MVXADTA_MITBAL ON (MVXADTA_MITMAS.MMCONO = MVXADTA_MITBAL.MBCONO) AND (MVXADTA_MITMAS.MMITNO = MVXADTA_MITBAL.MBITNO)


Thanks im Advance,

Ray.