mady
07-18-2007, 05:00 PM
Sup ppl.
So I have a little problem and I cant seem to find the answer.
I have a SQL query that works just fine:
SELECT parentid,convert(CHAR(10),calldatetime,110)as Ddate, crc,
count(crc) As total
from history
where history.calldatetime > '1/1/2007'
group by convert(CHAR(10),calldatetime,110), parentid, crc
Union
SELECT parentid,convert(CHAR(10),calldatetime,110)as Ddate, crc,
count(crc) As total
from historyarchive
where historyarchive.calldatetime > '1/1/2007'
group by convert(CHAR(10),calldatetime,110), parentid, crc
order by parentid , convert(CHAR(10),calldatetime,110)
and I need this in VBA , however I'm having trouble with the "Union" command and this code will give me data for only the first part of the query .
Sub AGRsqlDATA()
''' U need Microsoft ActiveX Data Objects Library
'Sales Adodb connction hit server1.
Dim conn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim Nsql1 As String
Dim i1 As Integer
Application.ScreenUpdating = False
' Create the Connection object.
Set conn1 = New ADODB.Connection
On Error Resume Next
With conn1
'Assign the connection string to the connection object.
.ConnectionString = "DRIVER={SQL Server};SERVER=tssql;UID=sa;" & _
"PWD=;DATABASE=touchstar"
' Open the connection.
.Open strConn1
End With
Nsql1 = "SELECT parentid,convert(CHAR(10),calldatetime,110)as Ddate, crc, " & _
"count(crc)as total " & _
"from history " & _
"where history.calldatetime > '1/1/2007' " & _
"group by convert(CHAR(10),calldatetime,110), parentid, crc " & _
"Union " & _
"SELECT parentid,convert(CHAR(10),calldatetime,110)as Ddate, crc, " & _
"count(crc) as total " & _
"from historyarchive " & _
"where historyarchive.calldatetime > '1/1/2007' " & _
"group by convert(CHAR(10),calldatetime,110), parentid, crc " & _
"order by parentid , convert(CHAR(10),calldatetime,110)"
' Create a new Recordset Object.
Set rst1 = New ADODB.Recordset
With rst1
' Connect this recordset to the previously opened connection.
.ActiveConnection = conn1
' Retrieve all records from the table.
.Open Nsql1, conn1, adOpenDynamic, adLockBatchOptimistic
End With
' Loop through all of the fields, returning the field names to the worksheet.
For i1 = 0 To rst1.Fields.count - 1
ActiveWorkbook.Sheets("sheet2").Range("A2").Offset(0, i1).Value = _
rst1.Fields(i1).Name
Next i1
' Copy the recordset to the new worksheet.
ActiveWorkbook.Sheets("sheet2").Range("A3").CopyFromRecordset rst1
' Close the recordset.
Set rst1 = Nothing
' Close the Connection.
conn1.Close
End Sub
Help would be greatly appreciated : pray2:
Edited 20-Jul-07 by geekgirlau. Reason: insert line breaks
So I have a little problem and I cant seem to find the answer.
I have a SQL query that works just fine:
SELECT parentid,convert(CHAR(10),calldatetime,110)as Ddate, crc,
count(crc) As total
from history
where history.calldatetime > '1/1/2007'
group by convert(CHAR(10),calldatetime,110), parentid, crc
Union
SELECT parentid,convert(CHAR(10),calldatetime,110)as Ddate, crc,
count(crc) As total
from historyarchive
where historyarchive.calldatetime > '1/1/2007'
group by convert(CHAR(10),calldatetime,110), parentid, crc
order by parentid , convert(CHAR(10),calldatetime,110)
and I need this in VBA , however I'm having trouble with the "Union" command and this code will give me data for only the first part of the query .
Sub AGRsqlDATA()
''' U need Microsoft ActiveX Data Objects Library
'Sales Adodb connction hit server1.
Dim conn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim Nsql1 As String
Dim i1 As Integer
Application.ScreenUpdating = False
' Create the Connection object.
Set conn1 = New ADODB.Connection
On Error Resume Next
With conn1
'Assign the connection string to the connection object.
.ConnectionString = "DRIVER={SQL Server};SERVER=tssql;UID=sa;" & _
"PWD=;DATABASE=touchstar"
' Open the connection.
.Open strConn1
End With
Nsql1 = "SELECT parentid,convert(CHAR(10),calldatetime,110)as Ddate, crc, " & _
"count(crc)as total " & _
"from history " & _
"where history.calldatetime > '1/1/2007' " & _
"group by convert(CHAR(10),calldatetime,110), parentid, crc " & _
"Union " & _
"SELECT parentid,convert(CHAR(10),calldatetime,110)as Ddate, crc, " & _
"count(crc) as total " & _
"from historyarchive " & _
"where historyarchive.calldatetime > '1/1/2007' " & _
"group by convert(CHAR(10),calldatetime,110), parentid, crc " & _
"order by parentid , convert(CHAR(10),calldatetime,110)"
' Create a new Recordset Object.
Set rst1 = New ADODB.Recordset
With rst1
' Connect this recordset to the previously opened connection.
.ActiveConnection = conn1
' Retrieve all records from the table.
.Open Nsql1, conn1, adOpenDynamic, adLockBatchOptimistic
End With
' Loop through all of the fields, returning the field names to the worksheet.
For i1 = 0 To rst1.Fields.count - 1
ActiveWorkbook.Sheets("sheet2").Range("A2").Offset(0, i1).Value = _
rst1.Fields(i1).Name
Next i1
' Copy the recordset to the new worksheet.
ActiveWorkbook.Sheets("sheet2").Range("A3").CopyFromRecordset rst1
' Close the recordset.
Set rst1 = Nothing
' Close the Connection.
conn1.Close
End Sub
Help would be greatly appreciated : pray2:
Edited 20-Jul-07 by geekgirlau. Reason: insert line breaks