I've a report which is based on a query. When I run a query, i get the results back pretty quickly (around 3, 4 sec.) The data set is about 6000 records. Now, when I run my report which comprises of 3 groups, it takes around a minute. I have also noticed that the status bar fills up quickly (indicating that the data is fetched) but then the control simply sits there for a minute before the report is displayed.
Anyone knows a specific reason for that and any advice how I can make things run faster? May be an new idea about my report. The report is simply. Its just that I have to show group totals at the three levels.
Are you joining records from multiple tables into one query and then applaying criteria in that one query? That slows it down somewhat since Access has to pull all those records, join then and then apply the cirteria.
If that is the case, create one query for the table (or minimum number of tables) that you are applying criteria to and then join then in on final query. Base your report on that final query.
I used to have one big query that took minutes to generate a report - it has criteria and calculations. Breaking it into several intermediate queries and performing as much of the calculations in the report brought that time to under a minute.
If you jave a lot of joins to do, split out those where you can apply criteria to first and then join them back.
No I have no other calculations in my report except for group totals. I tried reducing the number of group and now I'm down to 30 sec. instead of 60. but its still way more than 3 or 4 sec that its underlying query takes to run.
You mentioned 6000 records. In many circumstances, a query doesn't pull the entire dataset at once unless it has to when you view it on screen. To see if there is more to be pulled, press the "last record" button at the bottom. This forces the query to pull the entire dataset so that it can display the last page. The report has to pull the entire dataset at once and process each page so that it has an accurate count of total pages.
And the status bar is bogus - don't believe it. I have a number of reports that are "dogs" due to sluggish ODBC connections and oddly enough, the status bar zips up to full just as fast no matter how long the report actually takes to process (sometimes in the minutes).
The report is simply. Its just that I have to show group totals at the three levels.
If I understand correctly, your report is a summary report only of the 3 groups? If that is so, create a "Totals" query and use that on your report. This will conduct all of the processing in the query before running the report.
Thanks tcace. Your suggestion is valuable. I was also thinking on the same lines that to show 6000 records, Access must only have to pull all those records but also formats them too to display correctly (the group totals as well as grand totals). I'll do some mre testing and will post it here. It may help some one else too.
And I agree, the status bar is just crap... Any idea how to just completely remove it.