Running Balance on Pmt History

[dzdncnfsd]

I am creating a pmt history report. I need to show the customer's new balance after each transaction. The beginning balance is easy - it is the amount borrowed. The hard part is recalculating the balance after each pmt is applied. All I really need to do is add the amount paid, which is a negative amount, to the previous balance to get the new balance, but how do I keep refreshing the previous balance and new balance variables? I am learning VBA, so if someone can get me started, I am getting pretty good at fleshing out the rest. My first question is, where do I put the code? In the "OnOpen" portion of the Report section???
Thanks in advance.

Gladys Clemmer
gladys.clemmer@fifsg.com

 

[JerryDennison]

Use the Running Sum property. Create an unbound control that calculates your line total and set it's running sum property to yes.

 

[dzdncnfsd]

Thanks again, Jerry. I'll try it on Monday. Sure sounds simple enough.
Gladys

Gladys Clemmer
gladys.clemmer@fifsg.com

 

[MichaelRed]

For most "payment" functions, you need to know/implement the function(s) to calculate the interest ammount. There are several different methods, so you NEED to do the same thing the "OFFICIAL" accounting is doing. THEN, when a payment is received and recorded, calculate the interest, deduct this from the payment, and deduct this from the principal.

If there are any other 'factors', these must also be accounted for. Many institutions include penalties for late payment, some charge interest on a daily bassis from the date of previous payment to date of current payment, other 'fees' may be deducted from the payment before crditing the principal (insurance, taxes ...).

Ipso, ergo. BE SURE you are following the instutional 'rules' before letting information go out to a customer.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[dzdncnfsd]

Well, apparently this is not as easy as I thought. I am using two tables, one that contains the original balance, and one that contains daily transactions, which include the total amount paid, portion applied to principal, and portion applied to interest, but not the new balance after each transaction. I set up an unbound control (textbox, same thing, right?) on my report, but my only running sum options were "No, Over Group, or Over All". And I couldn't figure out how to link the unbound textbox to the running sum function so that it would know what it is summing. I also created a user defined function to calculate the new balance every time and put it in a module, then added that to the on_format section, but it didn't work, partly because I left the textbox unbound, and probably partly because the function isn't right anyway. Am I making this too hard, or am I really way off the mark of what I should be doing here? Help!!!!

Gladys Clemmer
gladys.clemmer@fifsg.com

 

[MichaelRed]

Gladys,

You should be almost 'there'. The text box probably is easiest done with a dlookup function. Just get the Orig Bal, and subtract the dlookup of the applied to principal. If you're using ver '97 the help is pretty good on dlookup. I can't find much of anything helpful in the 2K ver Help(less).


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[JerryDennison]

Do you have group levels set? If so, do you want the running sum to "reset" for each group? Then set the running sum to Over Group, if not set it to Over All. This control should be in the detail section of the report if you want the running sum to be calculated after each transaction(record). The control must be unbound. It shouldn't matter that the control is a calculated expression.

 

[dzdncnfsd]

Whew!! I finally got one of the payment histories to work, and it calculates the balance for each transaction. The second part of my task is to integrate it with another payment history that gets its information from a different database. (My company converted from one loan servicing application to another, the historical data is in the old, the current in the new, which is why I am doing this.)This second database carries a daily balance. I did a union query to put everything together, and it runs (without calculating balances). I then built a report based on it. The problem is, the records that need a calculated balance need the balance field set as a running sum, but the records from the second part of the query do not. Any suggestions? Please don't say subReport!! :-0
Thanks.

Gladys Clemmer
gladys.clemmer@fifsg.com

 

[MichaelRed]

Gladys,

Are you STILL dazed and confused? I know I AM. To even begin to sort it out / help, I would need a bit more detail? Lets break down your request and try to sort out some details.


Whew!! I finally got one of the payment histories to work, and it calculates the balance for each transaction. BUT, which history is this approximatly HOW is it being calculated?

The second part of my task is to integrate it with another payment history that gets its information from a different database. (My company converted from one loan servicing application to another, the historical data is in the old, the current in the new, which is why I am doing this.) Is the goal to simply do a one time conversion for the "Historical Data" to a format acceptable to the 'loan servicing application'? Or are you going to maintain both untill all of hte 'historical' loans are retired?


This second database carries a daily balance. I did a union query to put everything together, and it runs (without calculating balances). Why doesn't it calculate the balances? Is this one of the problems to solve? Did you deliberatly omit the balance information? If so, why? In the UNION query, are all of the fields from both recordsets the same type and are they all carried forward into the UNION query properly? Have you checked that the recordcount for the UNION query is equal to the sum of the record counts for the sources?


I then built a report based on it. The problem is, the records that need a calculated balance need the balance field set as a running sum, but the records from the second part of the query do not. Any suggestions?

Are the records which need this calculation redily identified within the query? Is there a "Source" field denoting WHERE the record originated? Can You ADD this field?

If So, add a (Hidden field in hte report which is the "running sum" for all records. For the records where you NEED this calc, set the report field = runningsum, but for the others, set it to the query field (use =IIF(DbFlag, SourceA, SourceB).


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[dzdncnfsd]

Michael,
I just started this long reply to you about why this or that won't work, but your questions made me rethink some of the things I am doing, so I am going to do a little more work before I throw in the towel.

I just want to say what a great help this forum has been to me. You guys don't have to waste your time helping people like me, but thank goodness you do. I just hope you get as much out of it as I do.

Thanks,
Gladys

Gladys Clemmer
gladys.clemmer@fifsg.com

 

[LMRollins]

Gladys,

Could you please tell me how you got your payment history to show the balance per transaction. I have the same setup as you. I have a table with the beginning balance and a table with daily transactions. I want to create a report that will show the running balance sort of like a checkbook. There will be deposits as well as withdrawals. My database is for petty cash.

 

[dzdncnfsd]

I left that company over a year ago, so don't have immediate access to the code any longer, but I believe that I did wind up using a running balance field on the report. However I am working on something similar to it today, so I will advise you if I find a better way to do it.

Gladys
Gladys Clemmer
gladys.clemmer@goldkist.com