Payment plan

[lution]

Ok, This may have been covered elsewhere, I'd love URL's to point look at.

I have a table in the system that lets the user add payment plan information with the following info: Start_Date (date plan starts), Days_Between (days between each payment, 30 is default for 1/month), Payment (amount due each payment), TotalDue (original amount due), PlanID (unique ID for the plan), PlanDescription (something for the user to put in notes).

I have another table that tracks payments that are actually made. It stores the date of the payment, amount paid, and which payment plan the payment was made against.

I'd like to create a payment plan report. Getting the rows for payments that are made was simple enough. Order them on payment date but haven't figured out how to track the declining balance with each row.

The bigger question is what's the best way to calculate and have a row for each future payment? Right now I create a temporary table in the front end and calculate each payment, declining balance, payment date, payment number and then report on that but it seems like a lot of work? Is there a better way? I don't want to create records for each planned payment when the plan is created because the plan/term might change and I hate tracking calculated info in the database.

Thanks

 

[PNGBill]

Reports allow for calculations to be done in the control.
This works great with Statements in that only the data is required and the running balance can be shown for each record (date)

You can also use grouping and have calculated controls for these within your report.

 

[lution]

Thanks PNGBill, but how do I get rows for the payments that haven't been made yet without creating dummy records in a table to display? Use VBA as part of the on open... hmm... ok I've got more digging to do.

 

[PNGBill]

Thanks PNGBill, but how do I get rows for the payments that haven't been made yet without creating dummy records in a table to display? Use VBA as part of the on open... hmm... ok I've got more digging to do.

We have a lending business and a loan, when created, adds the repayments due with dates etc as records in a table.
The query collecting data for the statement picks up these debit records.

If you do not store these but create the records in a query or Temp Table then again, the report query can collect this and it will be shown on the Statement.

We have a quite complicated Late Fee facility that calculates every occurrence where a Late Fee may be due. This data is added as records to a table (tblLateFees). A Statement can then be produced with all these records sorted by date order and running balance using the Report calculated control.

When the Late Fee calculation is done, the records in the table are deleted and new records added.
This avoids the table from having 20,000,000 records by year end and ensures the Late Fees are Up To Date as should a repayment by found (from unallocated funds) then previous Late Fee data may no longer be correct.
Actual Late Fees charged are not handled this way and of course are not deleted.

Trust this makes sence. Use a table for collecting data for a report but if and when it is no longer req'd, delete it at some point as it can be recreated should it be required again.