时间戳

首页 > 解决方案 > MS Access查询中如何获取运行扣除金额和运行余额？

sql - MS Access查询中如何获取运行扣除金额和运行余额？

问题描述

我一直在研究这个查询一段时间（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

从这些表中，我试图通过查询获得 RUNNING DEDUCTION 和 RUNNING BALANCE 以获得这种结果：（我将使用 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

在此示例查询中：

我试图实现的查询计算：

到目前为止，我能够使用这个获得运行总数

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_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 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;

先感谢您。

标签： sqlexcelvbams-accessms-access-2016

解决方案

我认为问题出在相关子查询中“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_posted”是一个布尔值（或是/否）。否则，将“is_posted”替换为“is_posted = TRUE”。

推荐阅读