Hi All,

I hav the follwing code (from KeepITcool) to get data
from two tables (cash & Credit)to my sheet1.

In the third Table (Rev) Tktno, SalRef are key fields and
I want get from that table ItemA, ItemB and ItemC
corresponding to Tktno, so my data will be from both
table cash and credit with additional fields from table
Rev.

both cash and credit Tktno are included in Table "Rev".

How can I modify my following code to get this.

I am very new to SQL (import data) and this is my first
attempt.


Dim sCmd()
Dim i%

ReDim sCmd(8)


sCmd(0) = _
" SELECT " & _
" c.arl, c.Tktno, c.bsp,c.SalRef, c.Issuedate," & _
" c.class, c.Reissueno, c.PassName, c.IRNum, " & _
" c.PubFare, c.ComFare, c.fd, c.A_fd, c.unpaid,c.Rev," & _
" c.tax1, c.tax2, c.tax3, c.slsman, "
sCmd(1) = _
" c.AddColl, c.AttrDscntVal, c.Target, c.Stock, " & _
" c.SpComVal, c.Net2Air, c.TourCode, " & _
" c.Remarks, c.Commissionable, c.prov, c.TicketType"
& vbCrLf
sCmd(2) = _
" FROM abcd.dbo.Cash c" & vbCrLf
sCmd(3) = _
" WHERE (c.bsp<'U') AND (c.Tktno Not Like '%I%')" & _
" AND (c.Issuedate={ts '" & Dt1 & " 00:00:00'} " & _
" AND c.Issuedate<={ts '" & Dt2 & " 00:00:00'})" &
vbCrLf
sCmd(4) = " UNION " & vbCrLf
sCmd(5) = sCmd(0)
sCmd(6) = sCmd(1)
sCmd(7) = Replace(sCmd(2), ".Cash", ".Credit")
sCmd(8) = sCmd(3)

For i = 0 To 8
Debug.Assert Len(sCmd(i)) <= 255
Next
Sheets(sh).Visible = True
Sheets(sh).Select
Range("A1").Select

With Selection.QueryTable
.Connection = _
"ODBC;DRIVER=SQL Server;SERVER=abc.com;UID=User;" & _
"PWD=pwd;APP=Microsoft Office 2003;WSID=USER"
.CommandText = sCmd
.Refresh BackgroundQuery:=False
End With


TIA
Soniya