Complex sequential query with vba functions takes a long time
Posted on 2013-05-31
I have a table where each record is a series of start and stop dates for each of the steps in a sequence of tasks (with 20 separate tasks per record, and about 1500 records). A couple of the tasks are optional, and there are some parallel paths (e.g. it always starts at step A, then goes through one or both of step B or "step C then step D" before meeting again at step E and so on - I don't think that in itself makes the calculation any harder, but it does tell us that the calculation required for each step is not consistent - for example, step D starts when step C finishes, but step E can only start when B and D have finished).
The table records progress as the steps are completed, but I then have to produce a forecast of the end date for the steps which have not completed (and mostly have not started). I have defined a query to generate that forecast.
The estimation for the forecast starts at the step in the sequence which has started but not yet completed - I estimate the end date for that step based on a known lead time for that step, and some user-entered delays (in a separate table) for that step. The calculation then proceeds sequentially - I take that estimated end date and use it as the estimated start date for the next step. I then calculate the estimated end date for that next step, and use that as the estimated start date for the next step after that, and so on.
My problem is that Access does not do the calculation sequentially - it appears to perform all of the calculations in the query at once, then realises that some of the fields rely on other fields which have changed, so recalculates those dependent fields, that then causes other dependent fields to recalculate and so on. Eventually the recalculation does not affect any other fields, and the query is complete. In a couple of the fields, the calculation is so complex that I have had to use VBA functions - I put some debug in one of them, and though it only need to be called 16 times to complete one record, it was actually called over 200 times.
I'm sure there is a better way to do this - I can't put all of the calculations in one big VBA function because I need to see those intermediate estimated start end end dates (to allow me to forecast when people will be needed to do the work in that particular step). Is there a way to make Access do the calculations in the correct order?