Is it possible to join a table and a sql statement "on the fly"

[Clipper2000]

I supposed joining a table plus creating a query "on the fly" as the proposed syntax below is not possible in Access. Does anyone know of a way to make this work?

SELECT Table1.Field1
from Table1 INNER JOIN (select Table2.Field1 from Table2) ON Table1.Field1 =(select Table2.Field1 from Table2).Field1

What I need to make this work is an alaise name for the *implied* query "select Table2.Field1 from Table2" (which do not exist yet). Assuming the name is QryTemp, then of course it will work such as:

SELECT Table1.Field1
from Table1 INNER JOIN (select Table2.Field1 from Table2) ON Table1.Field1 =QryTemp.Field1

So the question is how can I make Access aware of QryTemp before the query is run, assuming this solution is possible.

 

[Golom]

If you are using Access 97 then you need the embedded query as a separate query. In Access 2K or later however, the following works

SELECT Table1.Field1
from Table1 INNER JOIN 
(select Table2.Field1 from Table2) [COLOR=red][b]As QryTemp[/b][/color]
ON Table1.Field1 =QryTemp.Field1

 

[Clipper2000]

Thanks Golom, it worked.

 

[PHV]

And why not simply this ?
SELECT Table1.Field1
FROM Table1 INNER JOIN Table2
ON Table1.Field1 = Table2.Field1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244