Sub test()
Dim rst As Object
Dim rst1 As Object
Dim a As Double
Set rst = CurrentDb.OpenRecordset("SELECT tbl到账表.合同号, tbl到账表.客户名, Sum(tbl到账表.到账金额) AS 到账金额之合计 " _
& " FROM tbl到账表 GROUP BY tbl到账表.合同号, tbl到账表.客户名;")
Do Until rst.EOF
a = rst!到账金额之合计
Set rst1 = CurrentDb.OpenRecordset("select * from tbl还款表 where 合同号 = '" & rst!合同号 & "' AND 客户名 = '" & rst!客户名 & "' order by 还款期数 ", dbOpenDynaset)
Do Until rst1.EOF
rst1.Edit
If a > rst1!应还款金额 Then
rst1!实际还款金额 = rst1!应还款金额
a = a - rst1!应还款金额
Else
rst1!实际还款金额 = a
a = 0
End If
rst1.Update
rst1.MoveNext
Loop
rst.MoveNext
Loop
rst.Close
rst1.Close
Set rst = Nothing
Set rst1 = Nothing
End Sub
测试数据一样
因为考虑到账金额可能同一合同号有多笔 所以这里是汇总
"SELECT tbl到账表.合同号, tbl到账表.客户名, Sum(tbl到账表.到账金额) AS 到账金额之合计 " _
& " FROM tbl到账表 GROUP BY tbl到账表.合同号, tbl到账表.客户名;
自己视情况更改代码把
