How to calculate the running total of a column in a table in access using a query?

0
votes

I am beginner at MS access. I am making an access form to log and maintain loan payment. I have a main contact form where all the details such as a Unique ID, name, Loan amount, payment duration etc. of the person taking the loan are recorded. Now I have created another sub-form with table to record information of the amount recovered and installment paid. This table is linked to my main contact form through the Unique ID. As can been seen in the screenshot, I want a query that will automatically calculate the amount paid by a person and show what their outstanding amount is. I basically want the amount paid column to be a cumulative total of all the installments paid up to latest date. I tried the sum function in query but that didn't work. please help

Contact info

1
ms-access
This is a very common topic in numerous forums. Running sum in query and form is difficult. This is easily done on a report where textbox has RunningSum property. - June7

1 Answers

1
votes

You would need to use a correlated subquery such as:

select 
    t.*,
    (
        select sum(u.[amount paid])
        from YourTable u 
        where u.[u id] = t.[u id] and u.[payment no] <= t.[payment no]
    )
    as PaidSoFar
from 
    YourTable t

Change YourTable to the name of your table.