someone please explain why this (nonsensical) query works!

[Richard Vevay]

My impression is that the following query should not run / work, but it does.
The query is

select *
from tbl1 INNER JOIN qry1 ON tbl1.id = qry1.id
where
tbl2.field1 = "criteria2"

As it happens, tbl2.field1 is referenced in qry1. But shouldn't the
reference in the 'where' clause be to qr1.field1? I.e., shouldn't the
statement as written fail since tbl2 appears nowhere in the 'from' clause?
How can tbl2 be referenced in the 'where' clause??? Yet, this statement
works, and the selection results really are impacted by the last line of the
query.

For bonus points, explain why this SLIGHT modification to the above query
fails -- this modification attempts to reference tbl2 in the 'from' clause:

select *
from tbl3 INNER JOIN
(tbl1 INNER JOIN qry1 ON tbl1.id = qry1.id) ON tbl3.field1 = tbl2.field1
where tbl1.field1 = "criteria1" and
tbl2.field1 = "criteria2"

So the bottom line appears to be that a table which does NOT appear directly
in the 'from' clause may appear in the 'where' clause, but the same table may
NOT be referenced in the 'from' clause AS IF it had previously appeared. Is
this correct? Is this proper?

 

[Allen Browne]

For each field in a query, Access keeps track of the SourceTable.
To demonstate this:
1. Open the Northwind sample database that installs with Access.

2. Open the Immediate Window (Ctrl+G) and enter:
? Currentdb.QueryDefs("Orders Qry").Fields("City").SourceTable
You will see that Access knows that the City field is derived from the
Customers table.

If you need to take this one step further, create a new query:
SELECT [Orders Qry].* FROM [Orders Qry];
save it as Query1, and try:
? Currentdb.QueryDefs("Query1").Fields("City").SourceTable
Again, Access knows the source table, even though that table is not in
Query1.

That explains how JET is able to parse the WHERE clause.
Demonstrate with:
SELECT [Orders Qry].* FROM [Orders Qry] WHERE Customers.City = 'Berlin';

Even though this works, it could not be recommended as good practice. As you
point out, it is inconsistent (doesn't work in the FROM clause.) Worse, it
opens the door for bugs where the alias may not be interpreted correctly.
Then add the problems with Name AutoCorrect (where Access tries to track
previous names for tables, fields, captions, relations, etc), and it starts
to look like a mine field.

Hope that helps clarify what's going on. In answer to your final question,
what you have written is correct, but could not be considered a proper way
of doing it.

And just in case you need an even more confusing example, explain why this
query works in JET:
TABLE Customers;

 

[Richard Vevay]

Allen, thank you very much. If I understand this correctly, then it is true
that, as long as the underlying qry does not change -- either by selecting
tbl1.field AS something else, or by dropping all reference to tbl1 -- I can
rely on the fact that qry1.field1 will equal tbl1.field1. (Because they are
in fact the same field.) Okay, I pretty sure I know the answer now based on
all of this, but I am still looking for some validation.

For each field in a query, Access keeps track of the SourceTable.
To demonstate this:
1. Open the Northwind sample database that installs with Access.

2. Open the Immediate Window (Ctrl+G) and enter:
? Currentdb.QueryDefs("Orders Qry").Fields("City").SourceTable
You will see that Access knows that the City field is derived from the
Customers table.

If you need to take this one step further, create a new query:
SELECT [Orders Qry].* FROM [Orders Qry];
save it as Query1, and try:
? Currentdb.QueryDefs("Query1").Fields("City").SourceTable
Again, Access knows the source table, even though that table is not in
Query1.

That explains how JET is able to parse the WHERE clause.
Demonstrate with:
SELECT [Orders Qry].* FROM [Orders Qry] WHERE Customers.City = 'Berlin';

Even though this works, it could not be recommended as good practice. As you
point out, it is inconsistent (doesn't work in the FROM clause.) Worse, it
opens the door for bugs where the alias may not be interpreted correctly.
Then add the problems with Name AutoCorrect (where Access tries to track
previous names for tables, fields, captions, relations, etc), and it starts
to look like a mine field.

Hope that helps clarify what's going on. In answer to your final question,
what you have written is correct, but could not be considered a proper way
of doing it.

And just in case you need an even more confusing example, explain why this
query works in JET:
TABLE Customers;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

My impression is that the following query should not run / work, but it
does.
The query is

select *
from tbl1 INNER JOIN qry1 ON tbl1.id = qry1.id
where
tbl2.field1 = "criteria2"

As it happens, tbl2.field1 is referenced in qry1. But shouldn't the
reference in the 'where' clause be to qr1.field1? I.e., shouldn't the
statement as written fail since tbl2 appears nowhere in the 'from' clause?
How can tbl2 be referenced in the 'where' clause??? Yet, this statement
works, and the selection results really are impacted by the last line of
the
query.

For bonus points, explain why this SLIGHT modification to the above query
fails -- this modification attempts to reference tbl2 in the 'from'
clause:

select *
from tbl3 INNER JOIN
(tbl1 INNER JOIN qry1 ON tbl1.id = qry1.id) ON tbl3.field1 = tbl2.field1
where tbl1.field1 = "criteria1" and
tbl2.field1 = "criteria2"

So the bottom line appears to be that a table which does NOT appear
directly
in the 'from' clause may appear in the 'where' clause, but the same table
may
NOT be referenced in the 'from' clause AS IF it had previously appeared.
Is
this correct? Is this proper?

 

[Chris2]

For each field in a query, Access keeps track of the SourceTable.
To demonstate this:

Allen Browne,

That was fascinating. Thanks for posting that.


Sincerely,

Chris O>

 

[Allen Browne]

Rely on it? No: I wouldn't do that. Even if it works 100% at present, it's
the kind of thing that might not work in future versions, or if you
upsize/attach to a different database/use passthrough queries.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, thank you very much. If I understand this correctly, then it is
true
that, as long as the underlying qry does not change -- either by selecting
tbl1.field AS something else, or by dropping all reference to tbl1 -- I
can
rely on the fact that qry1.field1 will equal tbl1.field1. (Because they
are
in fact the same field.) Okay, I pretty sure I know the answer now based
on
all of this, but I am still looking for some validation.

For each field in a query, Access keeps track of the SourceTable.
To demonstate this:
1. Open the Northwind sample database that installs with Access.

2. Open the Immediate Window (Ctrl+G) and enter:
? Currentdb.QueryDefs("Orders Qry").Fields("City").SourceTable
You will see that Access knows that the City field is derived from the
Customers table.

If you need to take this one step further, create a new query:
SELECT [Orders Qry].* FROM [Orders Qry];
save it as Query1, and try:
? Currentdb.QueryDefs("Query1").Fields("City").SourceTable
Again, Access knows the source table, even though that table is not in
Query1.

That explains how JET is able to parse the WHERE clause.
Demonstrate with:
SELECT [Orders Qry].* FROM [Orders Qry] WHERE Customers.City =
'Berlin';

Even though this works, it could not be recommended as good practice. As
you
point out, it is inconsistent (doesn't work in the FROM clause.) Worse,
it
opens the door for bugs where the alias may not be interpreted correctly.
Then add the problems with Name AutoCorrect (where Access tries to track
previous names for tables, fields, captions, relations, etc), and it
starts
to look like a mine field.

Hope that helps clarify what's going on. In answer to your final
question,
what you have written is correct, but could not be considered a proper
way
of doing it.

And just in case you need an even more confusing example, explain why
this
query works in JET:
TABLE Customers;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

My impression is that the following query should not run / work, but it
does.
The query is

select *
from tbl1 INNER JOIN qry1 ON tbl1.id = qry1.id
where
tbl2.field1 = "criteria2"

As it happens, tbl2.field1 is referenced in qry1. But shouldn't the
reference in the 'where' clause be to qr1.field1? I.e., shouldn't the
statement as written fail since tbl2 appears nowhere in the 'from'
clause?
How can tbl2 be referenced in the 'where' clause??? Yet, this statement
works, and the selection results really are impacted by the last line
of
the
query.

For bonus points, explain why this SLIGHT modification to the above
query
fails -- this modification attempts to reference tbl2 in the 'from'
clause:

select *
from tbl3 INNER JOIN
(tbl1 INNER JOIN qry1 ON tbl1.id = qry1.id) ON tbl3.field1 =
tbl2.field1
where tbl1.field1 = "criteria1" and
tbl2.field1 = "criteria2"

So the bottom line appears to be that a table which does NOT appear
directly
in the 'from' clause may appear in the 'where' clause, but the same
table
may
NOT be referenced in the 'from' clause AS IF it had previously
appeared.
Is
this correct? Is this proper?