VBA用SQL计数的问题

满大人

如图所示，想根据sheet1的数据源，统计图中的数据，就是昨日和当月的入离职人数，帮忙看下怎么写，自己写了一部分不会了

wang-way

a筛选条件，b列分组条件，用字典或者sql

跨越时空

Sub lztj()
    Dim Conn As Object, rs As Object, SQL As String, strConn As String, CMonth%, Yday%
    Set Conn = CreateObject("ADODB.Connection")
    Sheet3.Activate
        strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source="
    Conn.Open strConn & ThisWorkbook.FullName
    CMonth = Month(Date)
    Yday = Day(Date - 1)
   
    SQL1 = "SELECT COUNT(离职日期) AS 离职人数 FROM [Sheet2$A:D] WHERE MONTH(离职日期)=" & CMonth & "and 员工分类='衡阳分中心'"
    SQL2 = "SELECT COUNT(离职日期) AS 离职人数 FROM [Sheet2$A:D] WHERE MONTH(离职日期)=" & CMonth & "and 员工分类='贵阳分中心'"
    SQL3 = "SELECT COUNT(离职日期) AS 离职人数 FROM [Sheet2$A:D] WHERE MONTH(离职日期)=" & Yday & "and 员工分类='衡阳分中心'"
    SQL4 = "SELECT COUNT(离职日期) AS 离职人数 FROM [Sheet2$A:D] WHERE MONTH(离职日期)=" & Yday & "and 员工分类='贵阳分中心'"
   
    Set rs1 = Conn.Execute(SQL1)
    Set rs2 = Conn.Execute(SQL2)
    Set rs3 = Conn.Execute(SQL3)
    Set rs4 = Conn.Execute(SQL4)
   
    With Sheet3
        .[D2].ClearContents
        .[D2].CopyFromRecordset rs1
        .[D3].ClearContents
        .[D3].CopyFromRecordset rs2
        .[C2].ClearContents
        .[C2].CopyFromRecordset rs3
        .[C3].ClearContents
        .[C3].CopyFromRecordset rs4
        
    End With
   
    rs1.Close
    Set rs1 = Nothing
      rs2.Close
    Set rs2 = Nothing
     rs3.Close
    Set rs3 = Nothing
      rs4.Close
    Set rs4 = Nothing
   
    Conn.Close
    Set Conn = Nothing
    MsgBox "OK"
End Sub

跨越时空

帮你写了上面部分，下面都是复制粘贴的事情。

Sub lztj()
    Dim Conn As Object, rs As Object, SQL As String, strConn As String, CMonth%, Yday%
    Set Conn = CreateObject("ADODB.Connection")
    Sheet3.Activate
        strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source="
    Conn.Open strConn & ThisWorkbook.FullName
    CMonth = Month(Date)
    Yday = Day(Date - 1)
    SQL1 = "SELECT COUNT(离职日期) AS 离职人数 FROM [Sheet2$A:D] WHERE MONTH(离职日期)=" & CMonth & "and 员工分类='衡阳分中心'"
    SQL2 = "SELECT COUNT(离职日期) AS 离职人数 FROM [Sheet2$A:D] WHERE MONTH(离职日期)=" & CMonth & "and 员工分类='贵阳分中心'"
    SQL3 = "SELECT COUNT(离职日期) AS 离职人数 FROM [Sheet2$A:D] WHERE MONTH(离职日期)=" & Yday & "and 员工分类='衡阳分中心'"
    SQL4 = "SELECT COUNT(离职日期) AS 离职人数 FROM [Sheet2$A:D] WHERE MONTH(离职日期)=" & Yday & "and 员工分类='贵阳分中心'"
   
    Set rs1 = Conn.Execute(SQL1)
    Set rs2 = Conn.Execute(SQL2)
    Set rs3 = Conn.Execute(SQL3)
    Set rs4 = Conn.Execute(SQL4)
   
    With Sheet3
        .[D2].ClearContents
        .[D2].CopyFromRecordset rs1
        .[D3].ClearContents
        .[D3].CopyFromRecordset rs2
        .[C2].ClearContents
        .[C2].CopyFromRecordset rs3
        .[C3].ClearContents
        .[C3].CopyFromRecordset rs4
    End With
    rs1.Close
    Set rs1 = Nothing
    rs2.Close
    Set rs2 = Nothing
    rs3.Close
    Set rs3 = Nothing
    rs4.Close
    Set rs4 = Nothing
    Conn.Close
    Set Conn = Nothing
End Sub

跨越时空

这个文件.zip (363.93 KB, 下载次数: 11)

2024-6-27 10:58 上传

点击文件名下载附件

Justin-2016

跨越时空 发表于 2024-6-27 10:38
Sub lztj()
    Dim Conn As Object, rs As Object, SQL As String, strConn As String, CMonth%, Y ...

学习了，用SQL解决问题还是直观点。

满大人

跨越时空 发表于 2024-6-27 10:38
Sub lztj()
    Dim Conn As Object, rs As Object, SQL As String, strConn As String, CMonth%, Y ...

我把几个日期改成了昨天，为啥入离职还是0

跨越时空

满大人 发表于 2024-6-27 21:24
我把几个日期改成了昨天，为啥入离职还是0

数据更新后要重新执行程序才能得到最新结果。

满大人

跨越时空 发表于 2024-6-28 07:57
数据更新后要重新执行程序才能得到最新结果。

不行，我把查询日期的month改成day可以，但是日期如果是去年的会一并计数