|
本帖最后由 张雄友 于 2014-5-10 19:55 编辑
任何一员工只要单价是没有的就不汇总。如某员工有多条记录,但是其中之一条记录没有单价,就不用汇总,即使某条记录有单价。- Sub SQL()
- Dim Conn As Object, Rst As Object
- Dim strConn As String, strSQL As String
- Dim i As Integer, PathStr As String
- Set Conn = CreateObject("ADODB.Connection")
- Set Rst = CreateObject("ADODB.Recordset")
- PathStr = ThisWorkbook.FullName
- Select Case Application.Version * 1 '
- Case Is <= 11
- strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr
- Case Is >= 12
- strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
- End Select
-
- Conn.Open strConn
- strSQL = "Select DISTINCT 工号 ,SUM(金额) AS 金额 From [明细$] WHERE 单价 > 0 Group By 工号 "
- '任何一员工只要单价是没有的就不汇总。如某员工有多条记录,但是其中之一条记录没有单价,就不用汇总,即使某条记录有单价。这里设置不对的?
- '
- Set Rst = Conn.Execute(strSQL) '
- With Sheets("放置") ''
- .Cells.ClearContents
- For i = 0 To Rst.Fields.Count - 1 '
- .Cells(1, i + 1) = Rst.Fields(i).Name
- Next i
- .Range("A2").CopyFromRecordset Rst
- .Cells.EntireColumn.AutoFit '
- .Cells.EntireColumn.AutoFit
- End With
- Rst.Close
- Conn.Close
- Set Conn = Nothing
- Set Rst = Nothing
- End Sub
复制代码
- strSQL = "Select DISTINCT 工号 ,SUM(金额) AS 金额 From [明细$] WHERE 工号 not in (Select DISTINCT 工号 From [明细$] WHERE 单价 is null and len(工号)>0) Group By 工号 "
复制代码
|
|