Report often takes multiple minutes to load, lots of formatting time between pages

[breezett93]

I'm hoping to find a solution to speeding up this frequently used report. Over the years, it has always been on the slower side, but as more and more gets added to it; the load times are now not acceptable. Is there an easy way for me to upload the report and example data where it takes ~10 minutes for the entire process to finish?

 

[dhookom]

There is an attach icon below the text entry box.

Does your report have “page of pages” or similar?
Where is the data coming from?
Are there subreports?

I have found in the past that I needed to create temp tables with records to print.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[breezett93]

I apologize; I don't know what the "page of pages" means.

The data is coming from a main query that pulls from two tables. Then there are several subreports that all have queries pulling from several other tables.

I tried exporting just the data that involves this report, but I'm getting errors on the exported file. This database is so old and interconnected that I don't think I'll be able to separate the pieces I'd like.
I also don't want to upload my entire database here. Is there a more secure option of getting it to you?

 

[dhookom]

It sounds like all of your application and data (Access tables) might be in one file. Can you tell us how many records are in your tables? Are we talking >10,000? Are the joins all on indexed fields?

By Page of Pages, I am referring to the ability to display page numbering on each page of the report. This often slows down reporting tremendously when there are controls that can grow as well as subreports.

If you can't figure out how to improve the speed, I would take a look at the file if needed. Please compact and repair the file and then zip and send to duanehookom AT gmail DOT com.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[breezett93]

My database is split with one front end and one back end. For this report, the largest table has 120,000 records but is only pulling 47 for the specific ID being searched. The smallest table has less than 10.

Below is the relationships for all the tables involved in this report.

Yes, it does look like the pages are printed on the report. It's set up as a text box with this string in it "="Page " & [Page] & " of " & [Pages]". I also have many controls enabled to grow/shrink as needed.

 

[dhookom]

Just for giggles, remove the pages expression.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016