You are here:

Using MS Access/How to create and update balance in field on form and table

Advertisement


Question
I have a table called LOANGRANTED, with the following fields; LOANID, CustomerName, TotalAmountDue, Payment, PaymentDate and Balance. I created a Querry called LOANPAYMENTS to deduce the balance. It is in this format;

Balance[TotalAmountDue]-[Payment].

But I realized this is not sufficient for my database. Assuming someone owes $1500 and pays $200, the balance according my Querry would be $1300 and that ends it! no room for an updated Balance.

I want a formulae or a VBA code which would reduce or update the Balance or TotalAmountDue whenever a payment is made by a specific client. A report should also be generated which would indicate; CustomerName, LOANID, PaymentDate, Payment and Balance.

Answer
You need two tables. One to store information about the loan in general, and a second table to store payments:

LoanT: LoanID, InitialBalance, CustomerInfo, etc.
1, 1000, Joe
2, 500, Bill
3, 2000, Sue

PaymentT: PaymentID, LoanID, Amount, PaymentDate, etc.
1, 1, 50, 1/1/13
2, 1, 25, 1/2/13
3, 1, 10, 1/3/13

This shows that you have 3 loans. Joe has made 3 payments on his loan in the amounts of 50, 25, and 10. Now you can use an aggregate query (or a form footer total, or DSUM) to total up those amounts and display it with the loan info.

I cover this kind of stuff in my Access Expert classes.


Be sure to visit my web site and watch my FREE 2-hour-long Microsoft Access Video Tutorial

Cordially,
Richard Rost
599CD Computer Training

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Richard Rost

Expertise

I am happy to answer any kinds of questions about Microsoft Access - from basic table design to advanced VBA programming. Also, please feel free to check the Access Tips & Tricks section of my web site, and free Access 101 tutorial.

Experience

I am the president of AccessLearningZone.com and 599CD.com. I specialize in Microsoft Access Tutorials. I have been teaching Access in the classroom since 1994, and online since 2002.

Education/Credentials
I am a self-taught Access expert. I have been building databases for clients since the early 90s. You can see a sample of my Access Tutorials on my web site at 599CD.com

©2016 About.com. All rights reserved.