How can I get Running Deduction Amount and Running Balance on MS Access Query?-CodePudding

I've been working on this query for a while now (MS ACCESS 2016)...

I have two related tables: Loan Table and Deduction Table

LOAN TABLE:

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 TABLE:

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

From these tables, I'm trying to get RUNNING DEDUCTION and RUNNING BALANCE through a query to get this kind of result: (I will populate this result into an Excel Userform ListBox using ADODB)

RUNNING DEDUCTION/BALANCE QUERY: [THIS IS THE DESIRED RESULT]

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

In this sample query:

the only DEDUCTION records displayed are those table_deduction.is_posted=True
also, the query should only include the deduction_amount with is_posted=True in the calculation of Running Deduction and Running Balance
It is sorted by date from newest to oldest

Query calculation I'm trying to achieve:

Running Balance = Loan Amount - Deduction Amount *then it will become... *
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.

CodePudding user response：

Change the nested SQL criteria to use t_deduction.is_posted instead of TD.is_posted - just remove 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,

CodePudding user response：

I think the problem arises from the identifier scope of "td.is_posted" within the correlated subquery. You may try the following code and let me know if it works:

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;

I am assuming that "is_posted" is a Bool (or Yes/No). Otherwise, replace "is_posted" by "is_posted = TRUE".