Querys based on Querys

[Gill]

I have querys based on querys in access, for example when I click a button
final query runs which is based on A Query , and because A query is based on
a B query and so on, I can get desired results in the final query (for
example in a report or form)
My question is, can this be done in code builder ? how to reference all
those queries at run time , like for example first the A query needs to be
resolved and then B query and C query and then finally the result is
displayed in the final query, Thanks

 

[tina]

the query or queries that the final query is based on, will run
automatically regardless of the method used to open the final query -
opening the final query via code is no different than opening it manually
from the database window.

hth

 

[Gill]

Well, I want to run all those queries in sequence when I click a button, so
basically I want to write a code in button click event , so that everything
is included there basically queries on the fly

 

[tina]

why? are these *Action* queries, that are appending, updating, or deleting
records in one or more tables? if not, then there's no point in opening
these queries; when you open the "final" query, it's still going to run all
other queries contained in its' SQL statement, or in the SQL statement of
any query(s) contained in its' SQL statement.

hth

 

[Gill]

Thanks for your continued help, (I apologise, I don't have a lot of
knowledge), do I have to write other queries SQL in where clause of final
query, Thanks

 

[tina]

your example in your initial post was, essentially: qryA is based on qryB,
and the final query (which i'll refer to hereafter as qryC) is based on
qryA.

if you open the qryC from the database window, you see the results of the
query. you do *not* have to open qryB and then qryA first; in fact, doing so
would make no difference - qryB and qryA will be run automatically as part
of the running of qryC, period.

as i said before, it doesn't matter *how* you run qryC - whether from the
database window, or from a macro or VBA code, or by opening a form or report
bound to qryC. you still get the same result - qryB and qryA will be run
*automatically* as part of the running of qryC.

hth

 

[Gill]

Thanks again, I think I need to further explain, Query C is based on Query B
and Query B is based on Query A, I want to write code in button click event.
I don't want to have permanent query B and query A

Right now, I have this set up , where I have query A , then query B and then
Query C , and like you said when I run Query C it works.

My question is how to write a code in the click event of a button, so that
all these queries are made on the fly (dont want to make query A and Query B
permanantly) and executed in sequence

I have read this article and was able to make the first query but dont know
how to make chain of queries in a sequence

http://www.fontstuff.com/access/acctut15.htm

Again thanks for your help

 

[tina]

okay, i see what you're saying now. but unfortunately it's not that easy;
you don't need three separate SQL statements running in VBA, you need one.
as an example, let's say you have qryA as a saved query object in the
database window, and you want to base a SQL statement on the query, in your
code. you could build a SQL string like this, as

strSQL = "SELECT SomeField, SomeOtherField " & _
"FROM qryA " & _
"ORDER BY SomeField;"

easy enough, right? but, as you said, you do NOT have qryA stored as a query
object in the database; instead, you want to also build qryA in the VBA
code. but you can't build and run it as a *separate* SQL string. you have to
replace "qryA" within strSQL with the actual qryA SQL statement. something
along the lines of


strSQL = "SELECT SomeField, SomeOtherField " & _
"FROM (SELECT * FROM SomeTable) As qryA " & _
"ORDER BY SomeField;"

here's where i have to stop. i doubt that the SQL statement above is the
correct syntax, but you can try it - and of course yours will need to be
even more complex, since your qryA SQL statement is based on a qryB SQL
statement. i've used nested SQL statements in the past, but my recent (16
months) experience is with A97, and i can't get nested SQL statements to
work in query objects or in VBA code, for the life of me. so i don't know if
the problem is with me, or some limitation of A97. at any rate, SQL is not
my strong point, so i'm not able to help you further, sorry.

sometimes other developers watch a thread without participating, so maybe
somebody else will step in to help. suggest you give it a day or so; then
start a new thread if necessary - making it clear that you're working with
SQL strings in VBA code, not saved query objects in the database. you can
refer to this thread if you want to, but don't expect others to find and
read this whole thread in order to answer your questions in a new thread.
good luck with it.

hth