MS Access query runs quickly, but a report tied to it takes 20 minutes to run?

Question

I have an MS Access query that runs in about 2 seconds and returns 82 records. When I try to create a basic report based on that query I run into problems. There are no pictures, etc in the report--it's simply displaying the query results. However, that report takes about 20 minutes to run and completely freezes Access while running.

Another odd thing is that I have a nearly identical query and it's report runs without any delay. The only difference between the query/report combo that works and the one that doesn't is one field in the queries. Both are date fields, but they're just stored on different tables.

Any ideas? I can't figure this out! Thanks in advance for any help!

Answer 1

2 seconds to retuns 82 records it's too slow.

anyway, have you open two query in the same time?
i mean, one for your 'view form' and one for your report ?
if that so, try to send your query to the report without open the 'view form', or close your 'view form' first before you make a report.
Even this is not a problem for most of database system, but it always worth to try.

some advice to increase speed query :
- Never use lookup table , use query join instead.
- Avoid to fetch all your records.
- Try not to open the table if you didn't need it.
- Avoid record count, use 'count' query instead.

Answer 2

If you are adding into the report a field that the query did not process from a different table. What access is doing is a JOIN of two tables. In order to do a JOIN access has to do lots of calculations in order to keep the integrity and normalization of the data. Base the result of the form only on the fields that are the result of the query and create the JOIN with in the query itself.
Hope this help you somewhat.