Correlated Subquery depth

  • Thread starter Thread starter R Avery
  • Start date Start date
R

R Avery

Suppose I have a correlated subquery whose inner query itself contains a
subquery. Instead of passing a field from the Outer Query to the inner
Query, I want to pass it to the subquery of the Inner query. Is this
possible? If so, how far down the chain can I pass fields from Outer
queries?


Outer Query
Inner Query
Sub-Inner Query


Any help would be most appreciated. Thanks!
 
Dear R:

This depends on two factors: whether you're using Jet or MSDE, and
just how lucky you feel.

Generally, using Jet, subqueries can reference only one level "in" so
your Sub-Inner Query could not reference a column in the Outer Query.
However, I believe I have occasionally seen this work.

There are often work-arounds for this, creating a query for the two
subqueries, then joining that query to the Outer. But there have been
a few instances where I have been stumped trying to figure a way to do
this.

If you're using MSDE, then my experience is you won't have any
problems at all. Just keep track of the aliases you use.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top