Query fast / Report slow

[bhipwell via AccessMonster.com]

This is a bit a repost, but new information (or basically an entirely new
problem) is available.

I have a pretty complex query that only takes a second to product the desired
result. The query is large made up of 16 or so other queries. The number of
queries is due to the calculations needed for each product to be displayed in
the query and eventually the report.

The report is for employee benefits. Therefore I have a query that
calculates payroll deductions, elections, etc. for each product. I am bring
these queries together to provide the data to create a single report bringing
all the information together.

As mentioned above, the query runs quickly. But the report is taking forever
to run.

The report is pulling about 70 pieces of data. About half the fields pulling
data have formatting for currency or code to provide a desired result if the
data in the query is insufficient.

The only VBA code includes about 12 sections only telling the report what
fields to make visible and to move the upward in the document if necessary.

Thoughts? Need help quickly!

BH

 

[Duane Hookom]

Is the report multiple pages? Do you have [Pages] in a control source?
You may want to create temporary tables and then report from them rather
than all the complex queries.

 

[John Spencer]

Can you comment out all the code in the report and then run the report to see
how long it takes?

If the report is still slow, then you have eliminated the code. If the report
is now fast,then you will have to add your code back in a segment at a time
until you pinpoint which bit of code causes the problem.

The only other thing I can think of is that when you run the report, Access
adds one more layer on to executing the query.

Also, I don't know if this will help at all, but make sure you have the Name
AutoCorrect features (Tools: Options: General tab) UNCHECKED in your database.

I won't say that is causing any problems, but it could be and is sometimes the
cause of weird little problems.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[bhipwell via AccessMonster.com]

Still stuck. I have determined it is not the report's code. Then I began
rebuilding the BIG query inside the report control source, hoping to avoid
that extra layer. I then added one small query at a time. As I added the
last few, the query itself and the report began slowing down dramatically.

Although the calculations or each small query are decent, it shouldn't be
slowing Access down. What I think is happening is all the queries are
running against every Employee. Then the BIG query filters to the employee
in question.

The only solution I can think of is to copy each small query and have them
automatically filter to the Employee in question. Then when added to the big
query, there should be less calculating time.

Thoughts?

BH