current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -2 down vote favorite

I wrote a code in excel VBA which was supposed to extract data from the SQL server. There is "tbl.id" has a relation in two tables. In my third columnI get the "cash-drop" and on the forth column I need total marker for that "tbl.id" from another table.I tried this SUM/GROUP in my code but I am getting out of luck. Maybe you can understand more if you have a look my excel vba codes:

    Sub GetingItFromSnd()
Dim DBFullName, TableName As String
Dim TargetRange As Range
Dim Conn As ADODB.Connection, intColIndex As Integer
Dim cel As Range
Dim TD As Long
Dim qdate1 As Double
Dim qdate2 As Double
Dim LastRow As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
qdate1 = Range("trddate1").Value
qdate2 = Range("trddate2").Value
Sheets("TblData").Range("A2:J20000").ClearContents
Sheets("TblData").Select
Columns("A:J").AutoFilter
Range("A2").Select
Selection.Activate
Set TargetRange = Range("A2")
Set Conn = New ADODB.Connection
 Conn.Open "driver={SQL Server};" & _
"server=XXsql;database=Csn;"
    Set RecSet = New Recordset
RecSet.Open "SELECT   sht_f.tbl_id, sht_f.s_openclose, sht_f.s_cashdrop,  " & _
"sht_f.s_current-sht_f.s_total+sht_f.s_cashdrop, SUM(hist_markers_per_tbl.TotalMarkers) ,  " & _
"replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(sht_f.tbl_id,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''), " & _
"sht_f.pt_name, sht_f.s_cashdrop/2.1, " & _
"(sht_f.s_current-sht_f.s_total+sht_f.s_cashdrop)/2.1 FROM sht_f sht_f, Csn.dbo.hist_markers_per_tbl hist_markers_per_tbl " & _
"WHERE hist_markers_per_tbl.game_date>='" & qdate1 & "' AND hist_markers_per_tbl.game_date<='" & qdate2 & "' sht_f.game_date>='" & qdate1 & "' " & _
"And sht_f.game_date<='" & qdate2 & "' And sht_f.pt_id<>'" & 99 & "'GROUP BY hist_markers_per_tbl.tbl_id ORDER BY sht_f.pt_name", Conn, , , adCmdText
    TargetRange.CopyFromRecordset RecSet
RecSet.Close
Set RecSet = Nothing
Conn.Close
Set Conn = Nothing
LastRow = Sheets("TblData").Range("A" & Sheets("TblData").Rows.Count).End(xlUp).Row
Columns("A:J").AutoFilter
Sheets("WPU").Select
End Sub
share|improve this question

1 Answer 1

up vote 0 down vote

It was very painful but I managed to find the answer to my question. Therefore wanted to share with all. Actually, I figured out that I was looking for "sub function" in my query:) Here is the finalized version of my code:

    Sub GetingItFromSnd()
Dim DBFullName, TableName As String
Dim TargetRange As Range
Dim Conn As ADODB.Connection, intColIndex As Integer
Dim cel As Range
Dim TD As Long
Dim qdate1 As Double
Dim qdate2 As Double
Dim LastRow As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
qdate1 = Range("trddate1").Value
qdate2 = Range("trddate2").Value
Sheets("TblData").Range("A2:J20000").ClearContents
Sheets("TblData").Select
Columns("A:J").AutoFilter
Range("A2").Select
Selection.Activate
Set TargetRange = Range("A2")
Set Conn = New ADODB.Connection
Conn.Open "driver={SQL Server};" & _
"server=XXsql;database=Csn;"
Set RecSet = New Recordset
RecSet.Open "SELECT   sht_f.tbl_id, sht_f.s_openclose, sht_f.s_cashdrop,  " & _
"sht_f.s_current-sht_f.s_total+sht_f.s_cashdrop, " & _
"replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(sht_f.tbl_id,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''), " & _
"sht_f.pt_name, sht_f.s_cashdrop/2.1, " & _
"(sht_f.s_current-sht_f.s_total+sht_f.s_cashdrop)/2.1," & _
"SUM(hist_markers_per_tbl.TotalMarkers) " & _
"FROM hist_markers_per_tbl hist_markers_per_tbl" & _
"WHERE sht_f.table_id= hist_markers_per_tbl.tbl_id AND hist_markers_per_tbl.game_date>='" & qdate1 & "' AND hist_markers_per_tbl.game_date<='" & qdate2 & "' GROUP BY hist_markers_per_tbl.tbl_id) " & _
"FROM sht_f sht_f, Csn.dbo.hist_markers_per_tbl hist_markers_per_tbl " & _
"WHERE hist_markers_per_tbl.game_date>='" & qdate1 & "' AND hist_markers_per_tbl.game_date<='" & qdate2 & "' sht_f.game_date>='" & qdate1 & "' " & _
"And sht_f.game_date<='" & qdate2 & "' And sht_f.pt_id<>'" & 99 & "'GROUP BY hist_markers_per_tbl.tbl_id ORDER BY sht_f.pt_name", Conn, , , adCmdText
TargetRange.CopyFromRecordset RecSet
RecSet.Close
Set RecSet = Nothing
Conn.Close
Set Conn = Nothing
LastRow = Sheets("TblData").Range("A" & Sheets("TblData").Rows.Count).End(xlUp).Row
Columns("A:J").AutoFilter
Sheets("WPU").Select
End Sub
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.