multiple make table queries to increase performance

[Guest]

Hello experts!

I am working with several queries and sub queries that build into 4-5 major
queries that get joined together to build a final master query that presents
the data in a format that is easy for others to understand. By using the
master query others can build simple ad hoc queries off the master without an
understanding of the underlying details.

As I begin to bring everything together I have noticed that performance is
beginning to be compromised. My thought is that I would like to build 4-5
make table queries that can be run without running each of them individually.
This will still allow me to look at the results and work on any of the 4-5
queries with stable data then join it all together with out compromising
performance for ad hoc reporting or when pulling the 4-5 queries together.

Please let me know if and how I can do this. If I am going about this all
the wrong way please let me know that as well.

 

[Jeff Boyce]

We're not there, and we can't see your data, so we can't determine
"right/wrong". A scan through this newsgroup, though, will probably show
that it is rare to need to build a make-table query.

Your post didn't get explicit about how many "several queries and sub
queries" actually is. And it sounds like you then use 4-5 more queries to
collect pieces, before collection those into your final result set. In all,
how many queries are involved?

Before looking to make-tables, are you certain you have indexed the
underlying tables on all fields used as either selection or join fields?

And if, after everything, you still need a table to hold intermediate
results, consider using a permanently-defined table that you use a delete
query on to clear out, and an append query on to refill.