This is a SQL question.
Try:-

SELECT
t.Account, t.Qty, t.Contract, t.Currency_Code,
t.Exchange, t.Futures_Date,
t.Premium, t.Strike_Price, t.Expiry_Date, t.PC,
c.Lots_Size, i.InstrumentCode
FROM Tickets t,InstrumentCodes i , contracts c
where
i.Contract = t.Contract
AND
i.Exchange = t.Exchange
AND
i.Currency = t.Currency_Code
and
c.Short_Code = t.Contract
AND
c.Exchange = t.Exchange
AND
c.Currency_Code = t.Currency_Code

and (Tickets.Account = '3MM') AND (Tickets.Contract
= 'ZNHG')

ORDER BY t.Account, t.Contract, t.Currency_Code,
t.Exchange, t.Futures_Date


Patrick Molloy
Microsoft Excel MVP


-----Original Message-----
Hi,

I want to be able to merge data from three data tables
from my underlying
sql database. My code works when executed as a stand-
alone statement fired
directly at the db but in VB it falls down with an error
dialog box showing
no error number!

The statement works fine when dealing with two tables
but not the third!

For the record here is the statement (spaced for
clarity) :-

SELECT
Tickets.Account, Tickets.Qty, Tickets.Contract,
Tickets.Currency_Code,
Tickets.Exchange, Tickets.Futures_Date,
Tickets.Premium, Tickets.Strike_Price,
Tickets.Expiry_Date, Tickets.PC,
Contracts.Lots_Size, InstrumentCodes.InstrumentCode
FROM Tickets

LEFT OUTER JOIN InstrumentCodes ON
InstrumentCodes.Contract =
Tickets.Contract AND InstrumentCodes.Exchange =
Tickets.Exchange AND
InstrumentCodes.Currency = Tickets.Currency_Code

INNER JOIN Contracts ON Contracts.Short_Code =
Tickets.Contract AND
Contracts.Exchange = Tickets.Exchange AND
Contracts.Currency_Code =
Tickets.Currency_Code

WHERE (Tickets.Account = '3MM') AND (Tickets.Contract
= 'ZNHG')

ORDER BY Tickets.Account, Tickets.Contract,
Tickets.Currency_Code,
Tickets.Exchange, Tickets.Futures_Date

Any thoughts?

Thanks.

James.


.