combine code to get data in one sheet
 

Hi All,
The follwing is the query code what i use in my sheet1
and sheet2

Can I combine this code and get both data in my sheet1
instead of sheet1 and sheet2?

I am taking identical fields from both tables as these
tables are kept for cash and credit and there are a few
un identical fields in these two tables.


With Selection.QueryTable
.Connection = _
"ODBC;DRIVER=SQL
Server;SERVER=abc.com;UID=User;PWD=pwd;APP=Microso ft
Office 2003;WSID=mycom"
..CommandText = Array( _
"SELECT Cash.arl, Cash.Tktno, Cash.tkttype, Cash.bsp,
Cash.SalRef, Cash.Issuedate, Cash.class, Cash.Reissueno,
Cash.Routeovrall, Cash.PassName, Cash.IRNum, Cash.P" _
, _
"ubFare, Cash.ComFare, Cash.fd, Cash.My_fd, Cash.unpaid,
Cash.Rev, Cash.tax1, Cash.tax2, Cash.tax3,
Cash.FuelSurcharge, Cash.slsman, " _
, _
"Cash.Txino, Cash.AddColl, Cash.MyDscntVal, Cash.Target,
Cash.Stock, Cash.SpComVal, Cash.Net2Air,
Cash.CashReceipt, Cash.TourCode, " _
, _
"Cash.Remarks, Cash.Commissi" _
, _
"onable, Cash.Face, Cash.prov" & Chr(13) & "" & Chr(10)
& "FROM Attar_Travel_2004.dbo.Cash Cash" & Chr(13) & "" &
Chr(10) & "WHERE (Cash.SalRef<'U') AND (Cash.Tktno Not
Like '%I%')" _
, _
" AND (Cash.Issuedate={ts '" & Dt1 & " 00:00:00'} And
Cash.Issuedate<={ts '" & Dt2 & " 00:00:00'})")

..Refresh BackgroundQuery:=False
End With



With Selection.QueryTable
.Connection = _
"ODBC;DRIVER=SQL
Server;SERVER=abc.com;UID=User;PWD=pwd;APP=Microso ft
Office 2003;WSID=mycom"
.CommandText = Array( _
"SELECT Credit.arl, Credit.Tktno, Credit.tkttype,
Credit.bsp, Credit.SalRef, Credit.Issuedate,
Credit.class, Credit.Reissueno, Credit.Routeovrall,
Credit.PassName, Credit.IRNum, Credit.P" _
, _
"ubFare, Credit.ComFare, Credit.fd, Credit.My_fd,
Credit.unpaid, Credit.Rev, Credit.tax1, Credit.tax2,
Credit.tax3, Credit.FuelSurcharge, Credit.slsman, " _
, _
"Credit.Txino, Credit.AddColl, Credit.MyDscntVal,
Credit.Target, Credit.Stock, Credit.SpComVal,
Credit.Net2Air, Credit.CreditReceipt, Credit.TourCode, " _
, _
"Credit.Remarks, Credit.Commissi" _
, _
"onable, Credit.Face, Credit.prov" & Chr(13) & ""
& Chr(10) & "FROM Attar_Travel_2004.dbo.Credit Credit" &
Chr(13) & "" & Chr(10) & "WHERE (Credit.SalRef<'U') AND
(Credit.Tktno Not Like '%I%')" _
, _
" AND (Credit.Issuedate={ts '" & Dt1 & "
00:00:00'} And Cedit.Issuedate<={ts '" & Dt2 & "
00:00:00'})")

.Refresh BackgroundQuery:=False
End With

TIA
Soniya

combine code to get data in one sheet
 

Sonya, as answered to an earlier post on the same subject
you'll have to use the UNION.

if you want two fields with different names to be selected
in the same field just alias it.

A simple test on Northwind..
SELECT CompanyName,ShipperID as ID
FROM Shippers
UNION
SELECT CompanyName, SupplierID AS ID
FROM Suppliers


If you had taken the time to format your snippet
so that people can easily cut and paste it from
a newsreader.. MAKE SURE the lines are no longer
than 70 chars (which allows for a bit of space
on quoted replies..)

ANd if you record these macro;s..
take some time to edit the code so that it's readable
(and fields names are not cutoff halfway in the string)

However the union string was too long for line continuance
so i've created the commandtext array in a different manner.

Cleaned code UNTESTED..

Sub Sonya()

Dim sCmd()
Dim dt1 As Date
Dim dt2 As Date
Dim i%

ReDim sCmd(8)

sCmd(0) = _
" SELECT " & _
" c.arl, c.Tktno, c.tkttype, c.bsp,c.SalRef, c.Issuedate," & _
" c.class, c.Reissueno,c.Routeovrall, c.PassName, c.IRNum, " & _
" c.PubFare, c.ComFare, c.fd, c.My_fd, c.unpaid,c.Rev," & _
" c.tax1, c.tax2, c.tax3, c.FuelSurcharge , c.slsman, "
sCmd(1) = _
" c.Txino, c.AddColl, c.MyDscntVal, c.Target, c.Stock, " & _
" c.SpComVal, c.Net2Air,c.CashReceipt as Receipt, c.TourCode, " & _
" c.Remarks, c.Commissionable, c.Face, c.prov" & vbCrLf
sCmd(2) = _
" FROM Attar_Travel_2004.dbo.Cash c" & vbCrLf
sCmd(3) = _
" WHERE (c.SalRef<'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) = Replace(sCmd(1), ".CashReceipt", ".CreditReceipt")
sCmd(7) = Replace(sCmd(2), ".Cash", ".Credit")
sCmd(8) = sCmd(3)

For i = 0 To 8
Debug.Assert Len(sCmd(i)) <= 255
Next

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

End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Soniya" wrote:

Hi All,
The follwing is the query code what i use in my sheet1
and sheet2

Can I combine this code and get both data in my sheet1
instead of sheet1 and sheet2?

I am taking identical fields from both tables as these
tables are kept for cash and credit and there are a few
un identical fields in these two tables.
<SNIP

combine code to get data in one sheet
 

Hi Dear keepITcool

Thank you very much for your kind reply.

Even though you had answered my query previously still it
confused me a little and i thought it may not work for me.

Thanks again for your repeated post

Soniya


-----Original Message-----
Sonya, as answered to an earlier post on the same subject
you'll have to use the UNION.

if you want two fields with different names to be
selected
in the same field just alias it.

A simple test on Northwind..
SELECT CompanyName,ShipperID as ID
FROM Shippers
UNION
SELECT CompanyName, SupplierID AS ID
FROM Suppliers


If you had taken the time to format your snippet
so that people can easily cut and paste it from
a newsreader.. MAKE SURE the lines are no longer
than 70 chars (which allows for a bit of space
on quoted replies..)

ANd if you record these macro;s..
take some time to edit the code so that it's readable
(and fields names are not cutoff halfway in the string)

However the union string was too long for line
continuance
so i've created the commandtext array in a different
manner.

Cleaned code UNTESTED..

Sub Sonya()

Dim sCmd()
Dim dt1 As Date
Dim dt2 As Date
Dim i%

ReDim sCmd(8)

sCmd(0) = _
" SELECT " & _
" c.arl, c.Tktno, c.tkttype, c.bsp,c.SalRef,
c.Issuedate," & _
" c.class, c.Reissueno,c.Routeovrall, c.PassName,
c.IRNum, " & _
" c.PubFare, c.ComFare, c.fd, c.My_fd,
c.unpaid,c.Rev," & _
" c.tax1, c.tax2, c.tax3, c.FuelSurcharge , c.slsman, "
sCmd(1) = _
" c.Txino, c.AddColl, c.MyDscntVal, c.Target,
c.Stock, " & _
" c.SpComVal, c.Net2Air,c.CashReceipt as Receipt,
c.TourCode, " & _
" c.Remarks, c.Commissionable, c.Face, c.prov" & vbCrLf
sCmd(2) = _
" FROM Attar_Travel_2004.dbo.Cash c" & vbCrLf
sCmd(3) = _
" WHERE (c.SalRef<'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) = Replace(sCmd
(1), ".CashReceipt", ".CreditReceipt")
sCmd(7) = Replace(sCmd(2), ".Cash", ".Credit")
sCmd(8) = sCmd(3)

For i = 0 To 8
Debug.Assert Len(sCmd(i)) <= 255
Next

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

End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Soniya" wrote:

Hi All,
The follwing is the query code what i use in my sheet1
and sheet2

Can I combine this code and get both data in my sheet1
instead of sheet1 and sheet2?

I am taking identical fields from both tables as these
tables are kept for cash and credit and there are a
few
un identical fields in these two tables.
<SNIP
.