Query within a Query

[nodge87]

Is there anyway of running a query which has been previously created from with another query???

Im not talkin about defining a sub query within a query.

I just mean referencing and running an outside query from within the one you are trying to run??

[GolferGuy]

You can use a pre-existing query within a new query in the same way you would use a table. But other than that, I have no idea of any other way (except the sub-query) to use another query within a query. If you are trying to get an Action query (Update, Delete, Append) to run from within another query, no that does not happen.

[nodge87]

Thanks GolferGuy. Ive just had to have the solution to the problem in steps of 4 queries to be executed manually 1 after the other.

Unfortunately I cannot get the 1st one to automatically ececute the other 3.

Thanks for your help anyway!

[GolferGuy]

Are you dealing with Action Queries (Update, Delete, or Insert)? If so, then you will have to write VBA code or a Macro to run the queries, or continue to do it manually. But if these are queries that are select or crosstab queries, then they will run "automatically" by referencing them as you would a table within another query. In this way, the 4th query would run the three other queries.

[nodge87]

The queries are not action queries. They merely count rows in another table so that I can use the counted values ina calculation later on. (Access would not allow me to divide by the count function for some reason so I had to count the rows in a table, export them to a seperate table and divide using the field name... If you get what I am saying?)

Ill try get them to run autoatically now and see what happens.

Another Quick Question... ......

Ive posted it in a seperate thread about validation.... Think this one is a none runner though..

[GolferGuy]

If you count the rows in one query, then use that query as the input to the next query (rather than outputting the results of the first query to a table), you can then do calculations on those row counts in the second query. Then that second query can be used as an input source for a third query, etc. There is a limit, but you will not know what it is until Access tells you that what you have put together is too complicated for Access to figure out.

[nodge87]

How do you do that using the SQL???

I have tried running the count query without sending the results to a table... and then in the 2nd query I tried referenceing the count but it wont let me???

Maybe I am doing the SQL wrong??

[GolferGuy]

Because you are "sending the results to a table" that means you are running an Append (insert) query, which is an Action query. Change that to a Select query, then you can use the query itself rather than a table in another query.

[nodge87]

So after changing it to a select query can i just reference the given field name for the count function?? Even if i change it from the default (Eg: Expr12)

[GolferGuy]

Yes, use that first query as though it were a table, at least as far as the second query goes. Try it before asking, it's a wonderful thing to see it work.

[nodge87]

Will do! Thanks a million!