SQL Code
 

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

SQL Code
 

Hi Soniya,

just a guess: do you have to finish your sql-statement (sCmd) with a
semicolon?

arno

SQL Code
 

Try:

SELECT *
FROM CASH A,
(SELECT *
FROM CREDIT B,
REV C
WHERE B.?=C.?
AND B.?=C.?) B
WHERE A.?=B.?

* = all columns. If you want specific collumnd, specify ALIAS.ColName e.g.
A.Tktno etc as required.

The WHERE clause specified the conditions to be satisfield for the table
JOINS. The ? stand for actual column names--replace as necessary.

"Soniya" wrote:

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

SQL Code
 

"AA2e72E" wrote ...

Try:

SELECT *
FROM CASH A,
(SELECT *
FROM CREDIT B,
REV C
WHERE B.?=C.?
AND B.?=C.?) B
WHERE A.?=B.?

* = all columns. If you want specific collumnd, specify ALIAS.ColName e.g.
A.Tktno etc as required.

I can't see that this solution would work. More than one column in
CREDIT is enough to break your code.

The WHERE clause specified the conditions to be satisfield for the table
JOINS.

Such join syntax has been superceded by SQL-92 INNER JOIN syntax e.g.

SELECT C.MyDataCol
FROM CREDIT B
INNER JOIN
REV C
WHERE ON B.MyKeyCol=C.MyKeyCol

I think even Oracle support this syntax now <g.

Jamie.

--

SQL Code
 

Oops! Make that:

SELECT C.MyDataCol
FROM CREDIT B
INNER JOIN
REV C
ON B.MyKeyCol=C.MyKeyCol

Jamie.

--