How to Divide Recordset ? (1 Viewer)

[PNGBill]

Hi Forum, MS Access 2010 ACDB.
A VBA task is to run through some 8,000 loans and check them for Late Fees. his is very slow on our current PC and I thought of breaking the task into say 4 groups.

This could be done by checking the next record and either Processing or going to the next record ie, if the Operator Selected A, B, C or D they would be ale to run the task 4 times.

Is there a simple Devide Rule that will cover all options over 4 (or 3) choices ?

Finding Odd or Even numbers will allow half of the Loans to be processed on each of two runs.

How to get this to 3,4 or 5 groups without missing any ??

Appreciate any advice.

ps, I could change the code - big task, but i 6 months our new pc will handle the full task in 40mins so a quick fix is all I need for now.

 

[Poppa Smurf]

Bill

1. What is average number of the 8,000 loans have late fees?
2. How long does it take to process an entry with a late fee?
3. Briefly what does the process a late fee involve e.g. update the existing record or append the data to another table etc. ?

Using the ODD and EVEN method would be the safest way.

Alternatively say you have 8000 + records you could have the following
Option A - records 1 to 2000
Option B - records 2001 to 4000
Option C - records 3001 to 6000
Option D - records 6001 to 8000
Option E - records 8001 to the end of the file

The number of options would depend on the number of records and these options would need to be displayed for the user to run.

 

[jdraw]

Please describe the "algorithm" that identifies a LateFee situation.

Suppose in your latest run you have identified all current LateFees, how would you identify those records that are potential LateFees in your next run?

What I'm getting at is -- maybe you don't have to process all 8000 records every time.

 

[PNGBill]

Thanks Poppa Smurf,
I came to the same conclussion and used an InputBox to get a number between 0 to 8 which will then only use the RecordSet where it is > & < accodingly - wrapped the code in this IF Then and it now handles the Up To 1,000 records in an aaceptable time.

Thanks jdraw, The RecorSet selects LoanID's where a number of criteria are met. of the 8,000 plus loans the RecordSet could be 1,500 ?? It changes as details of each loan may change from the last time the procedure was run.
Selecting the RecordSet is quick - maybe 20secs.

For each record, we must analyse each Fortnight since the Loan was issued and decide if a Repayment was Missed along with a few other issues as per the Loan Agreement.
This is the slow process as some loans could be 8 years old.
The reason we start a fresh calculation is to ensure any historical changes are considered eg A Repayment may have been in Unallocated and it was identified and move to the correct loan account since the last weekly run.
The Repayment Date remains when it was made and this could be years ago - although not often.

Our normal pc will handle this in < 1 hour yet my old pc takes 10 to 12 hours, hence the temp solution required.

 

[Galaxiom]

I would be wondering why the task is so slow.

Is part of the process opening single large recordset then filtering or finding within it? This is far, far slower than opening individual recordsets with the specific records thousands of times.

 

[Poppa Smurf]

For each record, we must analyse each Fortnight since the Loan was issued and decide if a Repayment was Missed along with a few other issues as per the Loan Agreement.
This is the slow process as some loans could be 8 years old.
The reason we start a fresh calculation is to ensure any historical changes are considered eg A Repayment may have been in Unallocated and it was identified and move to the correct loan account since the last weekly run.
The Repayment Date remains when it was made and this could be years ago - although not often.

I have a problem this statement in your post.

If a payment is missed etc. why would you change the historical entry? This would change any previous status reports?

A better method would be run the process and produce a snapshot (audit) of the loans, for any discrepancy add a manual repayment entry with the date on or after the run with the relevant adjustment. You could use these snapshots to rollback to a previous date for checking a date range etc.

Then the next time when run the process only check loans for transactions since the last run date in conjunction with the your snapshot data to give you the updated status of each loan.

This is a similar method to stock audit each year.

 

[PNGBill]

I would be wondering why the task is so slow.
Is part of the process opening single large recordset then filtering or finding within it? This is far, far slower than opening individual recordsets with the specific records thousands of times.

The Recordset is only the relevant LoanID's. it is not so large.
What needs to happen with each of the records is quite a process. That is what takes the time.
Is this what you mean with the faster method ?

 

[PNGBill]

Then the next time when run the process only check loans for transactions since the last run date in conjunction with the your snapshot data to give you the updated status of each loan.
This is a similar method to stock audit each year.

Running the procedure for loans that have had an Activity since the last Late Fee run would reduce the Recordset to process.
This would require some changes to our Database.
The Audit Trail would be an improvement.

All Repayments are taken from Bank Statements and if a entry is not identified, we put it into the Unallocated loan account.
The date is important as all Repayments for a given date must agree with the bank deposits for the same date.
We simply change the account number on the bank deposit from Unallocated to the correct loan account.

I guess you are suggesting some additional double Entry that would Debit Unallocated and Credit the correct loan account and same would be picked up on the next Late Fee Run to recalculate.

The reason we recalculate Late Fees is, in the above case, the fees would reduce and a credit Late Fee applied.