Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Strategy for a Complex Report
Strategies
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![[pc2]](/data/assets/smilies/pc2.gif "[pc2] [pc2]")
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