huge db, queries slow, best way to make queries run faster

[odun]

I have 5 different tables with a query for each one. The tables have some common fields, not all the fields are same from one table to the other.

After running the query, I would like to put the results of these 5 queries into 1 table and then run 1 query from this table based on billing date.

What is the best way of putting these 5 queries into one table? What I am doing right now is when I run each query, then I run another query to append to the final table. Is there a way to run these 5 queries and append them to the final table without having to run another query for each one? What would make my queries run faster?

The 5 tables are about 3 million rows in total and is making everything so slow. Please help.

 

[KenHigg]

My first question is - Are you bringing this all over to a local workstation or are you trying to do this across a network?

 

[odun]

I am currently running this on my desktop. The network is very slow and almost at its capacity.

My desktop still has some room on it.

Thanks again

My first question is - Are you bringing this all over to a local workstation or are you trying to do this across a network?

 

[KenHigg]

The simple way would be to do a macro to run them all of them with a single click...

 

[odun]

But, how would this make the queries run faster?

The simple way would be to do a macro to run them all of them with a single click...

 

[Pat Hartman]

Do you really need all the columns and all the rows? Surely no one would ever look at the detail records in a recordset this large. Why not apply the selection criteria as you extract data from the original tables?

 

[panthers1788]

Do you really need all the columns and all the rows? Surely no one would ever look at the detail records in a recordset this large. Why not apply the selection criteria as you extract data from the original tables?

Only Select the data that is actually being used. This should speed it up some.

 

[RMD]

Go to Task Manager then to Processes, right click on Access and set priority to real time. This will devote more of the computer's resources to Access. Note: other programs will run slower.

Also, if you query a query multiple times, make the first query a make table query.

 

[Galaxiom]

Firstly, 3 million records is not especially huge and should not be a problem in a properly set up table with well designed queries.

Secondly one would not normally append the results of the five queries into a table.

I suspect your issue will be the way you are selecting the dates. Are you applying a function to the date field of every record to determine if it is in a particular range (eg the current month)? This is a sure fire way to get a very, very slow query.

Describe your table structure and show the SQL of the queries.