SQL Multiple table joins in VBA

  • Thread starter Thread starter James
  • Start date Start date
J

James

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,
Click to expand...
Tickets.Expiry_Date, Tickets.PC,
 
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.
 
Back
Top