Is it possible to join derived tables to actual in Access 2007

Question

Is it possible to join derived tables to actual tables in Access 2007 like in SQL Server 2008? I have an example below:

  SELECT * 
    FROM 
    --MyOtherTable is a "real" table
    MyOtherTable INNER JOIN
    --MyTable is a "real" table. MyDerivedTable is a "pseudo" table derived from MyTable
    (SELECT * FROM MyTable WHERE MyID >10) AS MyDerivedTable ON
    MyOtherTable.ID = MyDerivedTable.ID

is this possible in Access? The reason I am asking is because I would like to migrate some of my SQL Server 2008 queries into Access but most of my queries contain a lot of derived tables and I haven't been able to convert my queries if they require the use of a derived table.

I found this link which seems to suggest that it is not possible....

http://www.access-programmers.co.uk/forums/showthread.php?t=78833

Can someone please confirm? Thanks in advance.

Answer 1

i have 2 tables in my access DB, customer and contracts and they are related through the customerid,

this is the script i use normally

SELECT Customer.CustomerName 
FROM Customer INNER JOIN Contracts ON Customer.CustID = Contracts.CustomerID;

and it works fine and this is how its represented in the design view

i changed my query to

SELECT Customer.CustomerName
FROM Customer INNER JOIN (select * from Contracts) derivedtable ON Customer.CustID = derivedtable.CustomerID;

similar to yours and it gave me same results and this is how its represented in the design view

i hope it clarifies

Answer 2

Tested in 2003 version and similar query seems to run fine.

It appears that when saving it, Access rewrites it, replacing the left and right parenthesis with [ and ]. like this. And she doesn't really like comments:

SELECT * 
    FROM 

    MyOtherTable INNER JOIN

    [ SELECT * FROM MyTable WHERE MyID >10 ]. 
        AS MyDerivedTable ON
    MyOtherTable.ID = MyDerivedTable.ID ;