INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Strategies

Strategy for a Complex Report by randysmid
Posted: 14 Dec 02 (Edited 23 Oct 03)

Sometimes we need to create a report out of data that is available, but is not in the format needed.  Typically, we would try to create a query where we put everything into it all at one time.  Sometimes, this is not the most efficient.  

I have decided to use a different approach, where I will work "backward" from the report.  I will design a layout of the data that is most efficient for the report.  With this layout, I will then create a series of one or more queries that will give me this data.  Since I might be accessing a table that is "live", I will use a "make-table" query, where I can retrieve the records that I want immediately.  An example of this might be a sales order system that contains thousands of orders from around the world, and we want the report to give us all worldwide sales.  This presumes that the European and Asian orders are in a remote (or different) database, but in the same format.  

So, the first query in the chain will go and retrieve only those orders from the USA system.  In other words, jump in quickly, get the summary data, write it out to a temporary table, then get out of the "live" table.   This temporary table can be used over and over, but be aware that you will get messages from the system asking if it is ok to delete the data that already exists.  If you wish to suppress those messages, you can simply add a single code statement prior to running the queries.  This code statement will be:
         DoCmd.SetWarnings False
   (at the end of your query statements, you should reset it with ôDoCmd.SetWarnings Trueö)

Next, we can append summary data from the European and Asian database with another two queries, as my sample below demonstrates.  Finally, using the result from the final query (as the recordsource to the report called "rptSalesReportWorldWide"), we can then run the report in "Preview" mode.  

Your set of code statements may end up looking like this:
      DoCmd.SetWarnings False
      DoCmd.OpenQuery ôqrySalesReportMakeTableUSASalesö
      DoCmd.OpenQuery ôqrySalesReportAppendEuropeanSalesö
      DoCmd.OpenQuery ôqrySalesReportAppendAsiaSalesö
      DoCmd.OpenReport ôrptSalesReportWorldWideö,acPreview
      DoCmd.SetWarnings True

Questions?  Send me an email and I will update the FAQ to include anything that is not understood.

Feedback, comments?  
Randy Smith, MCP
rsmith@cta.org    

Back to Microsoft: Access Reports FAQ Index
Back to Microsoft: Access Reports Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close