Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    Unanswered: graphs die as more data accumulates

    i think i need some help...

    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:
    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 )
          ...and graph
    
          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
          ...and graph
    
      next productGroup
    
      new page
    
    next customer
    thanks for your thoughts.

    izy

  2. #2
    Join Date
    Apr 2002
    Posts
    139

    Re: graphs die as more data accumulates

    Hi Izy,

    "Quote"
    the report is based on 10 nested queries that have a lot of calculation and multiple nested iif
    ...i almost forgive access.
    "Unquote"

    From my experiences, I strongly support your own idea of creating fixed tables somewhere halfway.
    It will most likely not only save your graphs, but speed up the query processing as well.

    hth

    Marion

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Thanks Marion,

    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.

    izy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •