我已经在这个查询上工作了一段时间了(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;
提前谢谢你。
更改嵌套的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,
我认为问题来自于"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".