If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Slow performance with joins, unions and multiple queries

Here's the overview:
My data resides in Excel files and I have to combine data from several tables in order to obtain the information I need. I also use multiple queries to get there. I know it's not an elegant design (I'm a novice), but it works relatively well except in those situations:

1. At the beginning I wanted to link to Excel files instead of importing them. That of course would save me the pain of having to re-import the Excel files every time I change them. It was terribly slow though.

2. In one instance, I have to use "Union All". Afterwards, queries that used to take 10 seconds take 5 minutes to complete.

Is there a way of turning a query into a table or stopping the recalculation process? I only need to update the data every few days. That means that I could generate some queries that build up to a table that contains all the information I need ("Inter-Table") and then "freeze" this result so all my subsequent queries would be based on "Inter-Table" and there would be no need to repeat all the steps that led to the "Inter-Table".

Instead of using a set of simple SELECT query joined by a UNION operation to retrieve the data, you could use a serie of SELECT...INTO queries that would each create a table and store the data from the SELECT data set into it. The complete syntax is (see Access help):

One other posibility is to import the data into a platform with a few more options , such as SQL Server. Based on your expertise level , this may require some learning , but in the long run will give you more flexibility