CODEWITHSUNDEEP
ms-access
GeoffDS
GeoffDS

Reputation: 1271

MS Access 2007 - Query inside query inside query inside query

I have a big Access database I am working on, designed by someone else. I am trying to improve it. The way it was set up, first you run Query 1 which makes Table A. Then, run Query 2, which used Table A among others, and made Table B. Then run Query 3, which uses Table B and makes Table C. Then run Query 4, which uses Table C and makes Table D. The final output we use is Table D.

My improvement here was to change Query 1, Query 2, and Query 3 to Select queries, instead of make table queries, and changing the SQL simply by find and replace (for example, in Query 2, replace all the instances of Table A with Query 1 instead). Query 4 is still a make table query and it makes Table D which I export to Excel later.

My question: Can I just run Query 4? That is, will that automatically run Query 3, which will automatically run Query 2, which will automatically run Query 1? Or, do I need to run Query 1 first, then run Query 2, then run Query 3, then run Query 4?

Perhaps beyond that, can I make Query 4 a Select query also? Then, I wouldn't even run Query 4. Instead, I would just export Query 4 itself to Excel and I'm wondering if that would automatically run Query 4, which would run Query 3, and so on.

Just to be clear: I'm not looking for any SQL tips here. I want to keep it just using Access (which is a front-end for SQL, I know) for now.

Note: I realize that one answer here is, "Why don't you try it?" I have and it seems to work as I think it should. The problem is, I have already run all the queries many times before, so I don't know if that makes everything work for now but maybe it won't work the same way later if I haven't already run the previous queries.

Thanks for any help

Upvotes: 1

Views: 3464

Answers (2)

Conrad Frix
Conrad Frix

Reputation: 52645

As Olivier already mentioned, yes selecting from SELECT queries works as you expect. You can actually test this by creating this function in a module

Function LogQueryCall(ByVal query As String)

    Debug.Print query & " " & Now

End Function

an then calling it from your queries e.g.

Query1 

   SELECT *, LogQueryCall("Query1") FROM Table1 ;

Query2

  SELECT *, LogQueryCall("Query2") 
  FROM Query1 
     INNER JOIN Table2 
     ON Query1.Field = table2.Field

Query3

  SELECT *, LogQueryCall("Query3") FROM Query2

Then you'll see the results in the Immediate Window 

Query1 01/03/2012 5:54:46 PM
Query2 01/03/2012 5:54:46 PM
Query3 01/03/2012 5:54:46 PM

Note the function will only get called once per query not once per row

Another option is to create 1 big query. This would use the contents of each query in the from clause like so.

SELECT *
FROM       
     (Select * FROM Table1) A
     INNER JOIN Table2 
     ON a.Field = table2.Field

Upvotes: 1

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112259

The short answer is, "Yes, you can nest SELECT queries and just export to Excel by just exporting the outer-most query result".

Queries can be used exactly like tables in many situations. The nested queries will execute their own SELECT statement and pass their result to the surrounding query as if it was a table.

If query2 calls query1 and
query3 calls query2 and
qyery4 calls query3
... then the queries will automatically be executed in the order shown below by passing their result to the next query when you execute query4:

table -> query1 -> query2 -> query3 -> query4

I assume that your queries look something like this:

query1: SELECT * FROM table;
query2: SELECT * FROM query1;
query3: SELECT * FROM query2;
query4: SELECT * FROM query3;

Upvotes: 1

Related Questions