如何获取MS Access查询的Running Deduction Amount和Running Balance ?&l

Question

我已经在这个查询上工作了一段时间了(MS ACCESS 2016)…

我有两个相关的表:贷款表和扣除表

贷款表:

loan_id   employee_id   loan_date   loan_amount  is_posted
-------   -----------   ----------  -----------  ---------
1           1        06/01/2019   15,000.00     True
2           4        06/01/2019    2,000.00     True

扣除表:

deduction_id  loan_id  deduction_date  deduction_amount  is_posted
------------  -------  --------------  ----------------  ---------
D1_1          1       01/15/2020         500.00         True
D1_2          1       01/30/2020         500.00         True
D1_3          1       02/15/2020         300.00         False
D1_4          1       02/28/2020         100.00         True
D2_1          2       01/15/2020       1,000.00         False
D2_2          2       01/30/2020         200.00         True
D2_3          2       02/15/2020         500.00         True

从这些表，我试图得到运行扣除和运行平衡通过查询得到这样的结果:(我将使用ADODB将此结果填充到Excel Userform ListBox中)

正在运行扣除/余额查询:[这是期望的结果]

deduction_id  loan_id  deduction_date  deduction_amount  RunDeduct    RunBal
------------  -------  --------------  ----------------  ---------  ---------
D1_4         1       02/28/2020         100.00       1,100.00   14,400.00
D1_2         1       01/30/2020         500.00       1,000.00   14,500.00
D1_1         1       01/15/2020         500.00         500.00   15,000.00
D2_3         2       02/15/2020         500.00         700.00    1,300.00
D2_2         2       01/30/2020         200.00         200.00    1,800.00

在这个示例查询中:

• 唯一显示的扣款记录是table_deduction.is_post =True
• 同时，在计算Running Deduction and Running Balance
时，查询中应该只包含is_post =True的deduction_amount
• 按日期从最新到最旧排序

---

查询计算我正在尝试实现:

• 运行余额=贷款金额-扣除金额*然后将成为……*
• 运行余额=以前余额-(最近公布)扣除金额
• 运行扣除额=(1)扣除额则变为
• 运行扣除额=上一次扣除额+(最近发布)扣除金额

---

到目前为止，我能够使用这个

获得运行总数

SELECT
TD.deduction_id,
TD.loan_id,
TD.deduction_date,
TD.deduction_amount,
(SELECT Sum(deduction_amount) FROM t_deduction WHERE TD.deduction_date >= deduction_date AND TD.loan_id = loan_id) AS RunnPaid,
TL.loan_amount-RunnPaid AS RunnBalance
FROM
t_loan AS TL
INNER JOIN
t_deduction AS TD ON TL.loan_id = TD.loan_id
ORDER BY
TD.loan_id, TD.deduction_date DESC;

但是每当我尝试将't_deduction。is_post '字段，它会弄乱整个查询。在计算中仍然包括"未发布"的记录。

SELECT
TD.deduction_id,
TD.loan_id,
TL.loan_amount,
TD.deduction_date,
TD.deduction_amount,
(SELECT Sum(deduction_amount) FROM t_deduction WHERE TD.deduction_date >= deduction_date AND TD.loan_id = loan_id AND TD.is_posted=True) AS RunnPaid,
TL.loan_amount-RunnPaid AS RunnBalance
FROM
t_loan AS TL
INNER JOIN
t_deduction AS TD ON TL.loan_id = TD.loan_id
WHERE
TD.is_posted = True
ORDER BY
TD.loan_id, TD.deduction_date DESC;

提前谢谢你。

Answer 1

更改嵌套的SQL条件以使用t_deduction。is_post而不是TD。is_post -删除TD.:

(SELECT Sum(deduction_amount) FROM t_deduction WHERE TD.deduction_date >= deduction_date 
AND TD.loan_id = loan_id AND is_posted=True) AS RunnPaid,

Answer 2

我认为问题来自于"td.is_posted"的标识符范围。在相关子查询中。你可以试试下面的代码，让我知道它是否有效:

SELECT
TD.deduction_id, TD.loan_id, TL.loan_amount,
TD.deduction_date, TD.deduction_amount,
(SELECT Sum(deduction_amount) FROM t_deduction WHERE TD.deduction_date >= deduction_date AND TD.loan_id = loan_id AND is_posted) AS RunnPaid,
TL.loan_amount-RunnPaid AS RunnBalance
FROM
t_loan AS TL
INNER JOIN
(SELECT deduction_id, loan_id, deduction_date, deduction_amount
FROM t_deduction
WHERE is_posted ) AS TD
ON TL.loan_id = TD.loan_id
ORDER BY
TD.loan_id, TD.deduction_date DESC;

我假设" is_postd& quot;是Bool(或Yes/No)。否则，替换&;is_posted&;by "is_post = TRUE".