View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
patrick molloy patrick molloy is offline
external usenet poster
 
Posts: 391
Default SQL Multiple table joins in VBA

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.


.