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.

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.


.

Thanks Patrick that works.

How bizarre though as your version is the same as mine just short hand sql
(apart from the inner join which I have tried changing anyway).

Oh well - end of the day it works and i'm very grateful.

Thanks again.

James.

"Patrick Molloy" wrote in message
...
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.


.