同樣的累計餘額需求,在 Access 2007 環境下以查詢 + DSum 實作。核心邏輯與 Access 通用版相同,關鍵在於 同日期多筆以自動編號 ID 排序。
餘額:
Nz(DSum("借","財務收支記帳","[日期] < #" & [日期] & "#"))
+ DSum("借","財務收支記帳","[日期] = #" & [日期] & "# And [ID] <= " & [ID])
- Nz(DSum("貸","財務收支記帳","[日期] < #" & [日期] & "#"))
- DSum("貸","財務收支記帳","[日期] = #" & [日期] & "# And [ID] <= " & [ID])
DSum("借", ... "[日期] < #...#"):累加所有早於本筆日期的借方總額。DSum("借", ... "[日期] = #...# And [ID] <= " & [ID]):累加本日期且 ID ≤ 當前列的借方 (同日排序)。Nz 處理無早期記錄時的 Null。此查詢僅適用於原始完整資料集。若在查詢中加入 WHERE 篩選 (如只看 2023 年),餘額計算基準會縮水,導致數值錯誤。
需篩選時建議:
SUM() OVER (PARTITION BY ... ORDER BY ...)。關鍵字:累計餘額、DSum、查詢、Access 2007、財務記帳