What's generally quicker?

[AndyHumpherson]

Hi

I know you may come back with a 'depends' answer but I'm designing a set of reports for my re-designed database. Before, each 'main' report had a number of (8-10) sub-reports each based on a seperate query, as well as sub-forms for pivot charts e.t.c.

While this tended to be very slow to produce (and I have done some work to better normalise and index the data in my database) i'm wondering if approaching it in a different way would be better.

My thoughts were to either
- Have 1 main query that brings all the information I need together then use lot's of calculated fields to filter that data further.
- Still keep the queries that extract information from my tables but do away with the sub reports.

Any help would be appreciated....

 

[AUGuy]

I've found that calculated fields within the report itself run slower than doing the calculation with a query then displaying the results within the report. Additionally, if you're developing several reports off the main query, you could append those results to a temp table and then run your different reports. This would make it so you didn't have to run the same query over and over.

 

[AndyHumpherson]

Thanks AUGuy

I'm trying to reduce the number of queries needed to support the reports and if I can write to a temp table then i'll give it a try.

Thanks