Large Calculation Options for Quick Results

[aussie92]

Hello all - My problem is that I have a lot of calculations and need some input on how to run them faster. Thanks for any help!



Current Situation: I have a database that creates a budget for Leases (generally 50+ years). Users check off a list of items (48 items so far) that increase a leases complexity over 6 phases (phases defined by user input and create a timeline of around 50 years, plus or minus 10 years). Each checkbox corresponds to a value (.01 to .1) that create a complexity number. That complexity number (called LOE_Sum for Level of Effort) is then multiplied by the amount of hours for each task. There are three different types of tasks and they are separated into three different tables: Base_Support, RPM_Support, and Tactical_Support. Each task table has various numbers of tasks that happen for different amounts of time... Theses time tables can be as simple as: "One hour every month for Phase 2 and 3" or as complex as: "Two hrs every quarter for the first 2 years of phase 4 and every 5th year of phase 5" ... those are just examples, but basically they are a pain. To add to the ridiculousness, there also are checkboxes that increase specific hours by a different value than corresponds to the LOE_Sum value. These values are held in a different table.

Problem Statement: I currently have a macro that runs after users input all the checkboxes. It does what is needed, but it seems very cumbersome. It has several loops, a lot of multi-dimension arrays, and has gotten so crazy that I am not even sure how I got it to work in the first time It creates a record in a data "master" table for each month of the lease. My obvious problem is that with these leases being 50+ years, having a macro run loop after loop for each month (600+ records) creates a major delay. Especially when running over the network... it can sometimes take 20 minutes to generate the report. I need a way to cut down that time, while still maintaining this amount of detail. Any ideas?

I thought of making it just run the loop for each year, but I need to query it for any time frame... For example, if people want to see a particular month, I would have to splice out the data... so I am not sure if that is a viable solution.

Thanks for reading... and any ideas / questions / suggestions are VERY appreciated! I am the only Access / VBA person at my work so I lack brainstorming opportunities

[rpeare]

Any chance you can supply a sample db? just create a copy and strip out anything not related to this problem specifically then compact/repair, zip it up and upload it.

From reading this I'm guessing your formulas are hard coded in other words you have VBA that is handling 'one hour every month for phase 2 and 3' and also code for handling '2 hours every quarter for the first 2 years of phase 4 and every 5th year of phase 5'

To me this is four different, simpler formulas

1 hour every month for phase 2
1 hour every month for phase 3
2 hours every quarter for the first 2 years of phase 4
2 hours every quarter for every 5th year of phase 5

what I've done in the past is break down formulas (or tried to) to their simplest components and created tables/data entry to handle the calculations so that everything is handled the same way. Maybe post your code you're currently running (or if it's super long creating a text file with the code then uploading that)

[aussie92]

Thanks for the reply and the PM...

So, I actually have everything in tables because I am the only "admin" and I need users to be able to edit everything. Basically for those formulas, I have a column that says "frequency" and the macro figures out what to do based on that. I cant have anything hardcoded (as far as numbers or values are concerned) because users need to be able to change any and all values.

Let me figure out how to remove data and still have it make sense! It may not be till Monday, but hey I appreciate any help!!