如何在MSAccessQuery中獲取運行扣減金額和運行余額？

我已經在這個查詢上作業了一段時間（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 用戶表單串列框中）

運行扣除/余額查詢：[這是所需的結果]

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

在此示例查詢中：

• 唯一顯示的 DEDUCTION 記錄是那些 table_deduction.is_posted=True
• 此外，查詢應僅在運行扣除和運行余額的計算中包含帶有 is_posted=True 的 deduction_amount
• 按日期從新到舊排序

我試圖實作的查詢計算：

• 運行余額 = 貸款金額 - 扣除金額 * 那么它將變成... *
• Running Balance = Previous Balance - (recent posted) Deduction Amount
• Running Deduction = (1st) Deduction Amount then it will become...
• Running Deduction = Previous Deduction Amount (recent posted) Deduction Amount

So far, I was able to get the running totals using this

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;

But whenever I try to associate the 't_deduction.is_posted' field, it messes up the whole query. It still includes the 'not posted' records in the calculation.

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;

Thank you in advance.

uj5u.com熱心網友回復：

更改嵌套 SQL 標準以使用 t_deduction.is_posted 而不是 TD.is_posted - 只需洗掉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,

uj5u.com熱心網友回復：

我認為問題出在相關子查詢中“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”。

標籤：sql excel vba ms-access ms-access-2016

上一篇：如何將兩個Worksheet_Change事件合并為一個

下一篇：如何在ggplot2中制作雙y軸