手工記帳的「借/貸/餘額」欄,在 Access 中可透過查詢自動產生累計餘額。關鍵在於:同日期多筆交易需以自動編號 ID 決定先後順序。
財務收支記帳,欄位:日期 (Date)、借 (Currency)、貸 (Currency)、摘要 (Text)、ID (自動編號/長整數)。餘額:
Nz(DSum("借","財務收支記帳","[日期] < #" & [日期] & "#"))
+ DSum("借","財務收支記帳","[日期] = #" & [日期] & "# And [ID] <= " & [ID])
- Nz(DSum("貸","財務收支記帳","[日期] < #" & [日期] & "#"))
- DSum("貸","財務收支記帳","[日期] = #" & [日期] & "# And [ID] <= " & [ID])
邏輯解析:
Nz 處理 Null)。SUM() OVER (ORDER BY ...) 視窗函數。關鍵字:累計餘額、DSum、跑馬燈帳、查詢設計、財務應用