When to use make table queries

[Randal]

I have a query that summarizes data from several different sources and puts
it in a summary table. A subsequent select query creates a grand total and
is used in a third query that give percent of total by category with a sub
query to display only the top 10 for each category. This third query also
makes a table, because it is so slow to open it with a report. Is there a
good rule of thumb as to when it is most efficient to use a make table query
vs. a select query?

 

[[MVP] S.Clark]

I use MTQ's only when I need to generate a table from unknown values. For
example, suppose you crosstab values at different intervals, and the column
names vary to the point that a static table will not hold the results.

On the other side, if the result set is static, then I will use a Delete and
Append query to populate the temporary table.

A SELECT query only returns records, so I'm not sure how this fits into your
question or the answer.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Randal]

That helps. What I was really concerned about is when it is inefficient to
use a query as a source for another query vs. creating a table from the
original query and using data from the table. I have found that if the
second query has a sub query and a query as a source of the data it really
slows down the process. Even though it takes longer to make a table than to
run a select query, it seems to be more efficient in my situation.

I just have a database so huge that I had to break it up into 4 and I am
trying everything I know to make it run faster.