Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    I have created a monster! (Access2K, Win2KPro)

    I am finishing up a rather complex project involving a couple of complex forms with a lot of data elements. The Annual Report requires a large amount of queries to output info for a variety of charts and graphs. In order to make it very easy for the user to format and 'massage' the report (that is, move graphs around, insert narrative, etc.) I set up the following structure in a directory:

    1) Construction of queries in Access (about 50)
    2) Set up External Data links in Excel to produce the charts
    3) Created linked charts in Publisher to allow the user to work on a nice, clean, well-formatted annual report

    So far so good, BUT...!!! There are 12 regions (and more to come), each of which requires the same data from their regional annual reports. If I was into brute force I would think about re-creating 50 sets of queries per region and setting up 12 separate Excel and Publisher links to wap out the regional reports. You can see this is not a great idea. You may also see how big this monster threatens to be...

    What I'd like to know is, are the sophisticated reporting ad-ons to Access that might allow creation of nice looking charts and export to, say, Word in a way that isn't nearly as brain-dead as Access' native reporting? I can get some ways out of the path of this Godzilla if I can report directly out of Access -- I went the Excel/Publisher route in the first place to get a better reporting module.

    Also, is there any advice for using VBA to dynamically create, name, export to charts and destroy queries? I'd like to not have even the 50 queries presently written for the single annual report. I'd like to dynamically create, use and destroy queries at run-time in VBA as this will give me all the control I get in query builder without stuffing Access with scads of dang saved queries.

    At this time, we're working on setting up a canned format for the annual report and for the regionals just throw region-specific data at whatever container is set up for the canned reports. I'd REALLY REALLY like an option native to Access that gives me a more robust reporting enviornment rather than doing things like, FSO to create Excel/Publisher docs from a Annual Report template for the 12 regionals. This is a special concern as there are more regions to be expected as time goes on, and a complex file structure for holding the regional reports sounds a bit too fragile, potentially.

    Any ideas?

    TIA!

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: I have created a monster! (Access2K, Win2KPro)

    You've raised lots of questions here, and I don't know all the answers (to say the least - many are opinions), so with that caveat:

    I don't know of any add-ins for Access (or anything else) that will do something as sophisticated as you are envisioning. There's no doubt that Excel charting and graphing is better than anything else in the Office suite, though there are some (expensive) packages that will do more sophisticated kinds of graphs. Publisher however would not be my first choice for doing the report - it's Word processing powers aren't nearly as robust as Word - though it has a few nice features that Word doesn't. But the real advantage of Word is that you can use automation to drive it from Access, and you should be able to automate the whole process of creating the charts and graphs in Excel and embedding them in Word. I've not played with the Publisher package to know anything about it's object model and how programmable it is, so I may be making an erroneous assumption. In either event, having a common format for the reports is the real key, and with good data normalization you should be able to use a standard set of queries and simply modify the parameters in order to do one region or another.

    As to you question about queries, you certainly can create queries on the fly in VBA, either by simply executing SQL Strings, or by creating a new QueryDef, saving it, executing it, and then destroying it. Another alternative specify just the parameter for a QueryDef - we do that frequently with stored queries where we supply a parameter to a stored query in VBA - of course you can also provide the parameter as a field on a form too. The advantage of stored queries is that they are compiled and optimized so if a query gets run repeatedly there are performance gains.

    Hope this provides some insights - others may want to chime in also.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: I have created a monster! (Access2K, Win2KPro)

    Well since posting my cry for help I have started to look into the world of DB reporting tools. You may know about Crystal Reports... I am looking at a variety of packages to see what might work best. I require something that supports graphing with db connection and output to RTF or something similar.

    Word is not a good choice for larger, more complex docs with a lot of charts -- at least, in my experience, it gets a bit dingy. Publisher is designed to do more in terms of layout and placement...Word tends to get confused with a lot of graphics. And, at any rate, I don't want to 'hard code' a set of word docs for each type of query.

    The advantage in a report writing tool is, the good ones allow you to set up queries outside of the host db, which will eliminate all these queries. The bad news is they aren't necessarily cheap! But it's starting to look like something like Crystal Reports is a solution. My client is willing to spend a little $$ to get nice reports. Well, I'm still looking into things...

    Thanks for the response!

    sps

Posting Permissions

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