Multiple subreports, speed, temporary tables?

[KMKelly]

I have a one page report that shows 24 months of data in rows at the top followed by various summaries: 6 month summaries since 2000 (6), current and past fiscal year and last 12 months, current fiscal year to date and past fiscal year to date AND then monthly averages of each of those summaries.

My standard query for the main 24 month list runs relatively quick, but the whole report with the subreports is taking 3+minutes. I have tried to combine summary periods where they don't overlap, but still have 10 different subtables - which account for 24 separate lines of summary. (Not my idea)

My boss, who is proficient with Fox Pro suggests making a temporary table and running all the queries off of that. I know I could do a make-table query and run the queries off of that, but I am concerned with multiple users, bloat, and where to put the table. The application is an MDE file with a SQL Server backend which is read only. If I create the new table locally, I am worried it might bloat the database and if I create it in another file, I have to be able to link to the table I just created, plus I need to arrange to name the table using the user name so if more than one person happens to be using it it won't go kablooey.

Any ideas? Has anyone had any luck with complex reports and lots of subreports?

Thanks for any input.

Kristin

 

[cfoust]

Are you talking about temporary tables in Access or in SQL Server? If you use a SQL Server back end, then create your temporary tables there. SQL Server will throw them away for you and they won't cause the Access database to bloat the way temp tables in Access would. On the other hand, MDEs don't retain the same kind of bloat the MDBs do.