Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Code
Hi Soniya,
just a guess: do you have to finish your sql-statement (sCmd) with a semicolon? arno |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Code
Oops! Make that:
SELECT C.MyDataCol FROM CREDIT B INNER JOIN REV C ON B.MyKeyCol=C.MyKeyCol Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
option buttons run Click code when value is changed via VBA code | Excel Programming | |||
VBA code delete code but ask for password and unlock VBA protection | Excel Programming |