i have a relatively complicated graphical report that works perfectly for the first four months of the year, starts to fall apart as data accumulates in the next few months, and becomes completely erratic in the final month so of the year.
i have understood the MS-KB admission that MS-Graph & MS-Access have timing problems, and I have implemented (...something very similar to) the recommended DoEvents loop in report.detail.format
with four months of data, i do not need DoEvents
with five months of data, 1,000 DoEvents calls does the job (100 does not)
with six months of data i use 10,000 DoEvents calls and ocassionaly drop a graph (5,000 does not work)
...clearly the trend cannot continue.
the report is based on 10 nested queries that have a lot of calculation and multiple nested iif
...i almost forgive access.
so far, the only option i can think of is to create temporary tables from the last-step query and base the graph on a simple query from the temp table. does anyone have an alternative suggestion (no matter how bizarre).
here's an outline (cannot post the real db unfortunately)
basics of the table structures:
month; contract; product; sales$
productGroup; product (each product belongs to one of the 5 product groups)
customer; Q1T, Q2T; Q3T; Q4T; Q1L; Q2L; Q3L; Q4L (T:Target, L:Lastyear by quarter) x productGroup
customer; contract (customer may have any number of contracts)
my graphics report looks like this in pseudo-code:
for each customer (sum all contracts belonging to this customer)
for each productGroup (sum all products in this productGroup)
sales$ by month
target by month (appropriate QT / 3 )
lastYear by month (appropriate QL / 3 )
accumulating monthly sales$ year-to-date
linear regression on accumulating sales$ projected to year end
accumulating monthly target to year end
accumulating monthly lastYear to year end
i improved things a touch by making each graph a separate subreport and stuffing the DoEvents in report.subreport.detail_format
This works with 50 DoEvents calls in each subreport and six months of data IN THE .MDB (the .MDE fails with any reasonable number of DoEvents!)
looks like a rewrite with temporary tables - i see your suggestion of temps at some midpoint, but i guess i'll take all the queries to ultimate conclusion for the temps. as it stands, preview any one report works just fine even with the query-spaghetti. this job only runs once a week so speed is not important.