Sql-server – Is it possible to join derived tables to actual in Access 2007

ms accessquerysql-server-2008subquery

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.

Best Answer

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

Normal Table

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

design view derived table

i hope it clarifies

Related Question