Help with reports 2

[dek394]

I have a query which takes about 5 minutes to run. I use this query to run 7 different reports which I want to link together. When I put them all into one report, it takes like 40 minutes for the main report to run. Is there any way I can put all of the reports into one main report and have the query run only once?

 

[dvannoy]

try a union query

 

[randysmid]

Hi,
Personally, I use a form to run reports in sequence. I like the user to click on a command button when he/she is ready to run these various reports. Here is a sample bit of code that will appear in the clicked event for the command button:
' acViewNormal sends report direct to printer
' acViewPreview allows the user to preview before printing
DoCmd.OpenReport "Report1", acViewNormal
DoCmd.OpenReport "Report2", acViewNormal
DoCmd.OpenReport "Report3", acViewNormal

HTH,
Randy Smith
California Teachers Association

 

[dek394]

Hey Randy,
Thanks for the response, I tried it out, but the query still runs for each report, is there any way for the form to run the query once, then use that information on all 7 reports?

Thanks,
Dennis

 

[dvannoy]

OK, you can do this two ways. Like I said a Union Query "OR" create all query's seperate then, create another query adding all the seperate querys into the new query then link your report to the new query. No matter how you do it, it may take some time depending on a few things. is the DB on a server??

 

[dek394]

I guess I dont understand this, because I only have 1 query running right now, so I can't link it to another one. All the reports run off of the exact same query, but the reports have different layouts. I just tried to run a smaller query off of the larger one but that didnt work. Either I didnt explain my problem well, or I don't understand the solution.

Dennis

P.S. the DB is on a server

 

[randysmid]

Hi,
Perhaps I don't understand either. I'm I correct in assuming that you want the recordsource for all these reports to be the same query? If so, that is a simple matter of setting the reports' recordsource property to the name of the query.

HOWEVER, if you are concerned about running the exact same query 7 times, and wish to make it more efficient, you can change the initial query to a "make-table" query, which would be used as a temporary table (e.g., "tblTempInfo&quot. Then, each report would have its' recordsource based on the temporary table. You can then use code like this:
DoCmd.OpenQuery "qryMaketblTempInfo"
DoCmd.OpenReport "Report1", acViewNormal
DoCmd.OpenReport "Report2", acViewNormal
DoCmd.OpenReport "Report3", acViewNormal

HTH,
Randy Smith
California Teachers Association

 

[dek394]

yeah, Randy and dvannoy, i ended up making a data dump table with the make table function. Thanks so much for your help, now I'm printing out the full report in about 4 and a half minutes.

Dennis

 

[WildHare]

Just to add my .02 cents worth, anytime you have two or more REPORTS that utilize the same basic record source, it will always be wiser to convert the recordsource into a table via a make-table query. Even if the reports don't have many fields in common, it will pay to shove them all in to one table and use it for the reports. Even if there is a small overhead penalty for a make-table query vs the simple select guy, running two reports against the select query will cause it to be executed TWICE, which is more than the query converted to a Make-Table will cost.

The only real drawback to using this method is the storage cost of the extra table, and the need to compact every now and then to recover the space used by the "previous" make-table.

Jim






Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm