How to replace a value while Edit?

[kathnicole]

Hi,

I am currently building a Loan Database for my office.
I have two tables; tblCustomer (with general Customer details)

and tblLoan (fields such as LoanAmount, LoanDate, Interest, InterestAmount, PreviousArrears , InterestPaidLas tMonth,Interest Balance,TotalAr rears and so on).

I could not come up with a solution for replacing the value for the field PreviousArrears in tblLoan.

PreviousArrears is the amount that was not paid until last month.
TotalArrears is sum of all balanced amount yet to be paid (it includes sum of PreviousArrears , Otherfees and InterestBalance )

I dont hav problem to enter the values for the first time. everything is working fine. but for the next month, if the customer paid the interest amount, then i need to make the corresponding changes.

say, the Total Arrears for the month of June is $100, i want that value to be replaced in PreviousArrears field if i try to enter values for the next month (July).

so that, PreviousArrears field will be $100 and TotalArrears Value will be changed to the new value based on PreviousArrears , Otherfees and InterestBalance

Need your assistance.

thanks,

[MMcCarthy]

The problem here is you are trying to store calculated amounts. These are normally calculated dynamically on a form or report but not stored in the database for the very reasons you've outlined.

Calculated values that are subject to change are a problem because it is very difficult to keep data integrity with them. How and when to trigger the update becomes a major issue.

[kathnicole]

Hi Mmccarthy,

thanks a lot for your reply.

the calculated value , in this case, does not affect the data i entered in a record.

the client pays the loan in an installment basis and all the interest amount which he pays every month must be in the database.

say, the customer XYZ is paying an interest of $100 every month. so,for the month August, if he pays just $80 then the arrears would be $20.

So for the month September, he should pay $120 (Interest + Arrears frm previous month)

I want the arrears information to be recorded when i attempt to enter the information for Spetember.

Could you please help me how should i get this done or please tell me if i did not understand what you have mentioned earlier.

thanks n regards,
Kathy

[MMcCarthy]

Hi Mmccarthy,

thanks a lot for your reply.

the calculated value , in this case, does not affect the data i entered in a record.

the client pays the loan in an installment basis and all the interest amount which he pays every month must be in the database.

say, the customer XYZ is paying an interest of $100 every month. so,for the month August, if he pays just $80 then the arrears would be $20.

So for the month September, he should pay $120 (Interest + Arrears frm previous month)

I want the arrears information to be recorded when i attempt to enter the information for Spetember.

Could you please help me how should i get this done or please tell me if i did not understand what you have mentioned earlier.

thanks n regards,
Kathy

The problem here is you can't just look at the record for the previous month. You need to identify this record somehow.

Assuming there is a record for June, what is the datefield that identifies this and how is that date stored?

How are new records added? For instance when you add the record for July do you do it through a form?

[kathnicole]

yes, PK for tblCustomer is Loan Number
PK for tblLoan is LoanID

the relationship between them is 1 to many .

i enter Customer details in frmCustomer and i have a button called "Enter Loan Info". when i press the button, it will open a form called frmLoan . when it opens, it updates the general fields like FirstName, LastName,Loan Number, Loan Amount from frmCustomer.

i hope you get an idea now.

thanks,
Kathy

[kathnicole]

Also, I identify a record based on a query which prompts the user to enter the LoanNumber and Month.

i have a autonumber field LoanID for tblLoan. i need to find the last record entered for particulat loan number and somehow i must retreive the value for TotalArrears.
Once i get the data, i should update that value into Previous Arrears field when i attempt to enter new record for the same loan number.

i dont know how to acheive this.

Kathy