时间戳

首页 > 解决方案 > 有没有办法使用 Excel VBA 中的 SQL 查询对复合键上的两个表进行完全连接？

sql - 有没有办法使用 Excel VBA 中的 SQL 查询对复合键上的两个表进行完全连接？

问题描述

我正在尝试在 Excel 中编写一个 SQL 查询，该查询使用复合键连接两个表 (Table A和)，并从 中返回匹配记录，以及任何不匹配的记录。Table BTable BTable B

两张表的维度不同，数据如下：

表 A

  ID      Date       Timestamp    Time Event Type   Time Event Type Desc.   ...
 ----- ----------- ------------- ----------------- ----------------------- -----
  123   10/5/2020    8:05:00 AM          P10               Clock In         ...
  123   10/5/2020   11:00:00 AM          P15               Meal Start       ...
  123   10/5/2020   11:30:00 PM          P25               Meal End         ...
  123   10/5/2020    6:30:00 PM          P20               Cock Out         ...
  ...      ...          ...              ...                 ...

表 B

  ID      Date      Scheduled Start   Scheduled End   ...   
 ----- ----------- ----------------- --------------- -----
  123   10/5/2020      8:00:00 AM      6:00:00 PM     ...
  123   10/6/2020     10:00:00 AM      4:00:00 PM     ...
  123   10/7/2020      9:00:00 AM      4:00:00 PM     ...
  123   10/8/2020     12:00:00 AM     12:00:00 AM     ...
  123   10/9/2020     12:00:00 PM      7:00:00 PM     ...
  124   10/5/2020      9:00:00 AM      4:00:00 PM     ...
  ...      ...            ...             ...

复合键是 ID 和日期的组合。使用下面的代码，我可以连接两个表并输出一个表，显示（对于他们每天打卡的每个 ID）计划的开始时间、打卡时间、计划的结束时间、打卡时间和其他一些列.

sql_query = _
        "SELECT [a].[ID], FORMAT([a].[Date], 'mm/dd/yyyy'), " & _
            "FORMAT([b].[Scheduled Start], 'hh:mm:ss AM/PM'), " & _
            "FORMAT(MIN(CDATE([a].[Timestamp])),'hh:mm:ss AM/PM') AS [Clock In Time], " & _
            "FORMAT([b].[Scheduled End], 'hh:mm:ss AM/PM'), " & _
            "FORMAT(MAX(CDATE([a].[Timestamp])),'hh:mm:ss AM/PM') AS [Clock Out Time] " & _
        "FROM [CLOCKINREPORT#csv] AS [a] " & _
        "LEFT JOIN [SCHEDULEREPORT#csv] AS [b] " & _
        "ON [a].[ID] = [b].[ID] AND [b].[Date] = [a].[Date] " & _
        "WHERE ([a].[Date] BETWEEN #" & Format(sWeekPer, "yyyy-mm-dd") & "# AND #" & Format(eWeekPer, "yyyy-mm-dd") & _
            "#) AND ([a].[Time Event Type] = 'P10' OR [a].[Time Event Type] = 'P20')" & _
        "GROUP BY [a].[ID], [a].[Date], [b].[Scheduled Start], [b].[Scheduled End] "

输出表

  ID      Date      Scheduled Start   Clock In Time   Scheduled End    Clock Out Time   ...   
 ----- ----------- ----------------- --------------- ---------------  ----------------
  123   10/5/2020     8:00:00 AM       8:05:00 AM      6:00:00 PM        6:30:00 PM     ...
  ...      ...            ...             ...             ...               ...

我还想在 ID 被安排但没有工作的日子显示记录（即，如果“123”被安排在“2020 年 10 月 6 日”但没有组合键、ID 和日期的组合，则不是中找到Table A，空值应显示在时钟输入时间和时钟输出时间列）。数据中存在的一个问题是，在 ID 没有打卡或打卡的日子里，没有与该日期相关的记录Table A。

期望的输出

  ID      Date      Scheduled Start   Clock In Time   Scheduled End    Clock Out Time   ...   
 ----- ----------- ----------------- --------------- ---------------  ----------------
  123   10/5/2020     8:00:00 AM       8:05:00 AM      6:00:00 PM        6:30:00 PM     ...
  123   10/6/2020    10:00:00 AM          NULL         4:00:00 PM           NULL        ...
  ...      ...            ...             ...             ...               ...

我的理解是 Microsoft ACE 12.0 不支持 aFULL JOIN并且需要 aUNION才能完成我想做的事情。我必须承认我是 SQL 的初学者，UNION我基于一些 stackoverflow 问题尝试过的 's 没有奏效。我将非常感谢解决此问题的任何帮助。

标签： sqlexcelvbams-access

解决方案

考虑MS Access 中的解决方法查询。FULL JOIN然而，为了可读性和可维护性，避免在 VBA 中出现混乱的连接、换行、引用处理和日期#附件。相反，将您的 SQL 保存在单独的 .SQL 文件中，或者为了提高效率，将查询保存在相应的 MS Access 数据库中并在日期上运行ADO 参数化。BETWEEN

SQL （另存为 MS Access 存储查询或 .SQL 文件；?参数占位符注意事项）

SELECT c.[ID]
     , FORMAT(c.[Date], 'mm/dd/yyyy')
     , FORMAT(s.[Scheduled Start], 'hh:mm:ss AM/PM')
     , FORMAT(MIN(CDATE(c.[Timestamp])),'hh:mm:ss AM/PM') AS [Clock In Time]
     , FORMAT(s.[Scheduled End], 'hh:mm:ss AM/PM')
     , FORMAT(MAX(CDATE(c.[Timestamp])),'hh:mm:ss AM/PM') AS [Clock Out Time]
       
FROM [CLOCKINREPORT#csv] AS c
LEFT JOIN [SCHEDULEREPORT#csv] AS s
   ON c.[ID] = s.[ID] AND s.[Date] = c.[Date]
WHERE (c.[Date] BETWEEN ? AND ?)
  AND (c.[Time Event Type] = 'P10' OR c.[Time Event Type] = 'P20')
GROUP BY c.[ID]
       , c.[Date]
       , s.[Scheduled Start]
       , s.[Scheduled End] 

UNION

SELECT s.[ID]
     , FORMAT(c.[Date], 'mm/dd/yyyy')
     , FORMAT(s.[Scheduled Start], 'hh:mm:ss AM/PM')
     , FORMAT(MIN(CDATE(c.[Timestamp])),'hh:mm:ss AM/PM') AS [Clock In Time]
     , FORMAT(s.[Scheduled End], 'hh:mm:ss AM/PM')
     , FORMAT(MAX(CDATE(c.[Timestamp])),'hh:mm:ss AM/PM') AS [Clock Out Time]
       
FROM [CLOCKINREPORT#csv] AS c
RIGHT JOIN [SCHEDULEREPORT#csv] AS s
   ON c.[ID] = s.[ID] AND s.[Date] = c.[Date]
WHERE (c.[Date] BETWEEN ? AND ?)
  AND (c.[Time Event Type] = 'P10' OR c.[Time Event Type] = 'P20')
GROUP BY s.[ID]
       , c.[Date]
       , s.[Scheduled Start]
       , s.[Scheduled End]

VBA （使用 ADO 连接 Access 数据库）

Sub RunSQL()
   ' REFERENCE THE MICROSOFT ACTIVEX DATA OBJECTS XX.X LIBRARAY '
   Dim conn As ADODB.Connection, cmd As New ADODB.Command, rs As ADODB.Recordset
   Dim StrQuery As String

   ' READ SQL QUERY FROM FILE
   With CreateObject("Scripting.FileSystemObject")
         StrQuery = .OpenTextFile("C:\Path\To\myQuery.sql", 1).readall
   End With

   ' OPEN DB CONNECTION
   Set conn = New ADODB.Connection
   conn.Open "DRIVER=Microsoft Access Driver (*.mdb, *.accdb);DBQ=C:\Path\To\Database\File.accdb;"
   
   ' DEFINE COMMAND OBJECT
   Set cmd = New ADODB.Command
   With cmd
       .ActiveConnection = conn
       .CommandType = adCmdText   ' OR BETTER IF USING SAVED QUERY: adCmdStoredProc
       .CommandText = StrQuery    ' OR BETTER IF USING SAVED QUERY: "myStoredAccessQuery"

       ' BIND 4 DATE PARAMETERS FOR FOUR ? IN SQL, ASSUMING sWeekPer AND eWeekPer ARE VBA DATES
       .Parameters.Append .CreateParameter("dtparam1", adDate, adParamInput, , sWeekPer)
       .Parameters.Append .CreateParameter("dtparam2", adDate, adParamInput, , eWeekPer)
       .Parameters.Append .CreateParameter("dtparam3", adDate, adParamInput, , sWeekPer)
       .Parameters.Append .CreateParameter("dtparam4", adDate, adParamInput, , eWeekPer)
   End With

   ' BIND OUTPUT TO RECORDSET
   Set rs = cmd.Execute

   ' ... USE rs AS NEEDED ...

   ' CLOSE AND RELEASE OBJECTS
   rs.Close: conn.Close
   Set rs = Nothing: Set cmd = Nothing: Set conn = Nothing
End Sub

推荐阅读