Running Sum Query

[snoopy92211]

have a table, Loans.

Fields:
ID (key)
Loan Number
Principal Balance.

I have another table, Transaction, that's based off the Loan Number field on the loans table.

Fields:
LoanNumber
Payment Type (2 choices, payment and advance)
Amount

I have a user form where users can specify the payment type, and amount, based on a certain loan number.

I'm attempting to do this:

User enters a transaction into the database. once the transaction is entered, the Principal Balance field for the specified loan number is updated.

If the payment type is advance, the query will subtract the amount from principalbalnce. if payment, the query wll add the amount to the principal balance.


Here's what I have so far.

The userform updates the transactions table with the information correctly.

My next step is to update the principal balance.

I'm thinking I could use some sort of update query, but I don't know where to begin.

HELP!!

If there is a better (normalized) way to accomplish this, I'm definitely open to ideas. Please just provide a basic example so I can understand.

 

[Cosmos75]

Since you aren't really loking for a running sum, but rather just a sum of all the transactions; where Advances are negative and Payments are positive (at least insofar as calculating the Loan Balance).

Why not have a table structure along the lines of the folowing;

tblLoan
LoanPK (Primary Key)
LoanID

tblPaymentType
PayTypePK (Primary Key)
strPayType

tblTransaction
TransactionID (Primary Key)
LoanPK (Foreign Key)
PayTypePK (Foreign Key)
curAmount

For the queries;

qryTransactions
- Makes curAmount negative if Payment Type is "Advance", which is "Transaction" is. In this case, the first record in tblPayType is Payment (PayTypePK=1)

SELECT LoanPK,
       PaymentTypePK,
       [curAmount],
       IIf([PaymentTypePK]=1,1,-1)*[curAmount] AS [Transaction]
FROM tblTransaction;

qryLoanBalance
- Calculate loan balance by adding up "Transcation" from qryTransactions.

SELECT qryTransaction.LoanPK,
       LoanID,
       Sum(Transaction) AS LoanBalance
FROM qryTransaction INNER JOIN tblLoan ON qryTransaction.LoanPK=tblLoan.LoanPK
GROUP BY qryTransaction.LoanPK, LoanID;

I'm am not saying this is the best way to do it, but it at least gives you an idea of how it can be broken up.

Hope this helps!