在VBA Access中按未知TaxCode值汇总TaxableAmount的方法

我有一个DAO.Recordset，其中TaxCode列存在十几种不同的值。需要在预先未知具体TaxCode值的情况下，计算每个TaxCode值对应的TaxableAmount字段总和。

示例输入数据 #

期望汇总结果 #

我已尝试以下代码：

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT TaxCode, TaxableAmount FROM dbo_MA_SaleDocDetail WHERE SaleDocID = " & SaleDocId)
rs.MoveLast
rs.MoveFirst

Do While Not rs.EOF
    For Each Field In .Fields
     if...?
Loop

解决方案1：直接用SQL GROUP BY查询（推荐） #

利用数据库原生的分组聚合能力，直接通过SQL获取汇总结果，效率远高于手动遍历Recordset：

Dim rsSummary As DAO.Recordset
Dim sqlStr As String

' 构建分组查询SQL
sqlStr = "SELECT TaxCode, Sum(TaxableAmount) AS TotalTaxableAmount " & _
         "FROM dbo_MA_SaleDocDetail " & _
         "WHERE SaleDocID = " & SaleDocId & " " & _
         "GROUP BY TaxCode"

Set rsSummary = CurrentDb.OpenRecordset(sqlStr)

' 遍历结果处理（示例：输出到立即窗口）
If Not rsSummary.EOF Then
    rsSummary.MoveFirst
    Do While Not rsSummary.EOF
        Debug.Print "TaxCode: " & rsSummary!TaxCode & ", 应税金额总和: " & rsSummary!TotalTaxableAmount
        rsSummary.MoveNext
    Loop
End If

' 清理资源
rsSummary.Close
Set rsSummary = Nothing

解决方案2：遍历现有Recordset用字典汇总 #

如果已经获取了原始Recordset，不想重新发起查询，可以用Scripting.Dictionary存储每个TaxCode的累加值：

Dim rs As DAO.Recordset
Dim taxSumDict As Object
Dim currentTaxCode As Variant
Dim currentAmount As Double

Set taxSumDict = CreateObject("Scripting.Dictionary")

' 打开原始记录集
Set rs = CurrentDb.OpenRecordset("SELECT TaxCode, TaxableAmount FROM dbo_MA_SaleDocDetail WHERE SaleDocID = " & SaleDocId)

' 遍历记录集累加金额
If Not rs.EOF Then
    rs.MoveFirst
    Do While Not rs.EOF
        currentTaxCode = rs!TaxCode
        ' 用Nz处理空值，避免计算报错
        currentAmount = Nz(rs!TaxableAmount, 0)
        
        If taxSumDict.Exists(currentTaxCode) Then
            taxSumDict(currentTaxCode) = taxSumDict(currentTaxCode) + currentAmount
        Else
            taxSumDict.Add currentTaxCode, currentAmount
        End If
        
        rs.MoveNext
    Loop
End If

' 清理记录集资源
rs.Close
Set rs = Nothing

' 输出汇总结果（示例：输出到立即窗口）
Dim key As Variant
For Each key In taxSumDict.Keys
    Debug.Print "TaxCode: " & key & ", 应税金额总和: " & taxSumDict(key)
Next key

' 清理字典资源
Set taxSumDict = Nothing

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