如何通过单条VBA查询获取7张库存表的sum(quantity)与sum(amount)？

要一次性获取每个库存项在7张表中的sum(quantity)和sum(amount)，我们可以通过构建一个关联多个统计子查询的SQL语句，再用VBA执行这个查询来实现。下面是具体的解决方案：

前提假设 #

我假设所有表都有一个共同的库存项标识字段（比如ItemCode，如果你的字段名不同，记得替换成实际名称），以及quantity和amount字段用于统计。

核心SQL逻辑 #

我们会为每个表单独写一个分组统计的子查询，然后用LEFT JOIN把这些子查询关联起来——这样即使某个库存项在某张表中没有记录，也能保留其结果（对应统计值为Null，后续可以用Nz()函数转为0）。

完整的SQL语句模板如下：

SELECT 
    COALESCE(S.ItemCode, I1.ItemCode, I2.ItemCode, I3.ItemCode, O1.ItemCode, O2.ItemCode, O3.ItemCode) AS ItemCode,
    Nz(S.Stock_Qty, 0) AS Stock_Total_Qty,
    Nz(S.Stock_Amt, 0) AS Stock_Total_Amt,
    Nz(I1.In1_Qty, 0) AS In1_Total_Qty,
    Nz(I1.In1_Amt, 0) AS In1_Total_Amt,
    Nz(I2.In2_Qty, 0) AS In2_Total_Qty,
    Nz(I2.In2_Amt, 0) AS In2_Total_Amt,
    Nz(I3.In3_Qty, 0) AS In3_Total_Qty,
    Nz(I3.In3_Amt, 0) AS In3_Total_Amt,
    Nz(O1.Out1_Qty, 0) AS Out1_Total_Qty,
    Nz(O1.Out1_Amt, 0) AS Out1_Total_Amt,
    Nz(O2.Out2_Qty, 0) AS Out2_Total_Qty,
    Nz(O2.Out2_Amt, 0) AS Out2_Total_Amt,
    Nz(O3.Out3_Qty, 0) AS Out3_Total_Qty,
    Nz(O3.Out3_Amt, 0) AS Out3_Total_Amt
FROM 
    (((((
        (SELECT ItemCode, SUM(quantity) AS Stock_Qty, SUM(amount) AS Stock_Amt FROM tblStock GROUP BY ItemCode) AS S
        LEFT JOIN (SELECT ItemCode, SUM(quantity) AS In1_Qty, SUM(amount) AS In1_Amt FROM tblIn1 GROUP BY ItemCode) AS I1 ON S.ItemCode = I1.ItemCode)
        LEFT JOIN (SELECT ItemCode, SUM(quantity) AS In2_Qty, SUM(amount) AS In2_Amt FROM tblIn2 GROUP BY ItemCode) AS I2 ON COALESCE(S.ItemCode, I1.ItemCode) = I2.ItemCode)
        LEFT JOIN (SELECT ItemCode, SUM(quantity) AS In3_Qty, SUM(amount) AS In3_Amt FROM tblIn3 GROUP BY ItemCode) AS I3 ON COALESCE(S.ItemCode, I1.ItemCode, I2.ItemCode) = I3.ItemCode)
        LEFT JOIN (SELECT ItemCode, SUM(quantity) AS Out1_Qty, SUM(amount) AS Out1_Amt FROM tblOut1 GROUP BY ItemCode) AS O1 ON COALESCE(S.ItemCode, I1.ItemCode, I2.ItemCode, I3.ItemCode) = O1.ItemCode)
        LEFT JOIN (SELECT ItemCode, SUM(quantity) AS Out2_Qty, SUM(amount) AS Out2_Amt FROM tblOut2 GROUP BY ItemCode) AS O2 ON COALESCE(S.ItemCode, I1.ItemCode, I2.ItemCode, I3.ItemCode, O1.ItemCode) = O2.ItemCode)
        LEFT JOIN (SELECT ItemCode, SUM(quantity) AS Out3_Qty, SUM(amount) AS Out3_Amt FROM tblOut3 GROUP BY ItemCode) AS O3 ON COALESCE(S.ItemCode, I1.ItemCode, I2.ItemCode, I3.ItemCode, O1.ItemCode, O2.ItemCode) = O3.ItemCode

VBA执行代码示例 #

下面是用DAO（适用于Access）执行这个查询并将结果存入记录集的示例代码：

Sub GetStockStats()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sqlStr As String
    
    ' 构建SQL语句（如果你的字段名不同，替换对应的部分）
    sqlStr = "SELECT " & _
             "COALESCE(S.ItemCode, I1.ItemCode, I2.ItemCode, I3.ItemCode, O1.ItemCode, O2.ItemCode, O3.ItemCode) AS ItemCode, " & _
             "Nz(S.Stock_Qty, 0) AS Stock_Total_Qty, Nz(S.Stock_Amt, 0) AS Stock_Total_Amt, " & _
             "Nz(I1.In1_Qty, 0) AS In1_Total_Qty, Nz(I1.In1_Amt, 0) AS In1_Total_Amt, " & _
             "Nz(I2.In2_Qty, 0) AS In2_Total_Qty, Nz(I2.In2_Amt, 0) AS In2_Total_Amt, " & _
             "Nz(I3.In3_Qty, 0) AS In3_Total_Qty, Nz(I3.In3_Amt, 0) AS In3_Total_Amt, " & _
             "Nz(O1.Out1_Qty, 0) AS Out1_Total_Qty, Nz(O1.Out1_Amt, 0) AS Out1_Total_Amt, " & _
             "Nz(O2.Out2_Qty, 0) AS Out2_Total_Qty, Nz(O2.Out2_Amt, 0) AS Out2_Total_Amt, " & _
             "Nz(O3.Out3_Qty, 0) AS Out3_Total_Qty, Nz(O3.Out3_Amt, 0) AS Out3_Total_Amt " & _
             "FROM " & _
             "(((((" & _
             "(SELECT ItemCode, SUM(quantity) AS Stock_Qty, SUM(amount) AS Stock_Amt FROM tblStock GROUP BY ItemCode) AS S " & _
             "LEFT JOIN (SELECT ItemCode, SUM(quantity) AS In1_Qty, SUM(amount) AS In1_Amt FROM tblIn1 GROUP BY ItemCode) AS I1 ON S.ItemCode = I1.ItemCode) " & _
             "LEFT JOIN (SELECT ItemCode, SUM(quantity) AS In2_Qty, SUM(amount) AS In2_Amt FROM tblIn2 GROUP BY ItemCode) AS I2 ON COALESCE(S.ItemCode, I1.ItemCode) = I2.ItemCode) " & _
             "LEFT JOIN (SELECT ItemCode, SUM(quantity) AS In3_Qty, SUM(amount) AS In3_Amt FROM tblIn3 GROUP BY ItemCode) AS I3 ON COALESCE(S.ItemCode, I1.ItemCode, I2.ItemCode) = I3.ItemCode) " & _
             "LEFT JOIN (SELECT ItemCode, SUM(quantity) AS Out1_Qty, SUM(amount) AS Out1_Amt FROM tblOut1 GROUP BY ItemCode) AS O1 ON COALESCE(S.ItemCode, I1.ItemCode, I2.ItemCode, I3.ItemCode) = O1.ItemCode) " & _
             "LEFT JOIN (SELECT ItemCode, SUM(quantity) AS Out2_Qty, SUM(amount) AS Out2_Amt FROM tblOut2 GROUP BY ItemCode) AS O2 ON COALESCE(S.ItemCode, I1.ItemCode, I2.ItemCode, I3.ItemCode, O1.ItemCode) = O2.ItemCode) " & _
             "LEFT JOIN (SELECT ItemCode, SUM(quantity) AS Out3_Qty, SUM(amount) AS Out3_Amt FROM tblOut3 GROUP BY ItemCode) AS O3 ON COALESCE(S.ItemCode, I1.ItemCode, I2.ItemCode, I3.ItemCode, O1.ItemCode, O2.ItemCode) = O3.ItemCode"
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sqlStr)
    
    ' 这里可以添加处理记录集的逻辑，比如遍历输出到工作表或者保存到临时表
    If Not rs.EOF Then
        ' 示例：输出到立即窗口
        Do While Not rs.EOF
            Debug.Print "Item: " & rs!ItemCode & _
                        " | Stock Qty: " & rs!Stock_Total_Qty & " | Stock Amt: " & rs!Stock_Total_Amt & _
                        " | In1 Qty: " & rs!In1_Total_Qty & " | In1 Amt: " & rs!In1_Total_Amt
            rs.MoveNext
        Loop
    End If
    
    ' 清理对象
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

关键说明 #

• COALESCE函数：用于从多个表的ItemCode中获取非空值，确保每个库存项都有唯一的标识。如果你的数据库不支持COALESCE（比如旧版Access），可以替换为Nz()嵌套，比如Nz(S.ItemCode, Nz(I1.ItemCode, Nz(I2.ItemCode, ...)))。
• Nz函数：将统计结果中的Null转为0，避免后续数据处理出现错误。
• LEFT JOIN：保证即使某个库存项在某张表中没有交易记录，也能在结果集中显示，对应的统计值为0。

如果你的表结构有特殊情况（比如没有统一的ItemCode字段、字段名不同），可以根据实际情况调整SQL和VBA代码。

内容的提问来源于stack exchange，提问作者Sohaib Elahi