Queries from Queries from Queries – General Question.

[sanders720]

Currently, I have three queries. The second is dependent on information from the first, and the third is dependent on information from the second (and thus the first). I would now like to code this in VB using sql statements. I would also like to not have to depend on running query #1 to do query #2, etc. My question is, what would be the syntax.

Below is a conceptual example.

1. sql1=”SELECT JobNo, Hours FROM tblJobs”
2. sql2=”SELECT Hours FROM qry1”
3. sql3=”SELECT HOURS FROM qry2 WHERE tblJobs.Hours >10”

This is of course an extremely simple example, and I realize I probably don’t need three queries to get this information. If you could think of a better example, please use it. Anyhow, I’m just looking for the right syntax.

Thanks in advance for the help…

 

[jrbarnett]

"SELECT HOURS FROM tbljobd WHERE tblJobs.Hours >10"

will give you the results of query3 taking the results from query 1

John

 

[mpastore]

Sanders,

Sometimes it is best to have separate queries as in your example, it tends to make the process more readable.

What you can do is create queries like qry01, qry02 in the database then refer to them in your VBA code. You could also create the queries in your code and save then via the querydef object, but this may be less efficient.

Mike Pastore

Hats off to (Roy) Harper

 

[mpastore]

Sanders, go with John's example, I had my nose too far into the theoretical, and missed the practical.

Mike Pastore

Hats off to (Roy) Harper

 

[jrbarnett]

Having said that, there are times when the only alternative to collecting piles of queries on top of queries are huge numbers of correlated subqueries as I had in a commercial application once; using 3 queries on top of each other, each pulling in another table of data was faster than 4 correlated subqueries on the table.

John