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

    --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....


Can someone please confirm? Thanks in advance.

  • it seems to me like I am going to have to just create a bunch of tables or separate queries manually in Access so I can point to them and join on them in my queries as opposed to being able to write simple queries like I do in SQL server.
    – Juan Velez
    Jun 26, 2012 at 16:27
  • 1
    Your query as it is, will not in any DBMS. You have a SELECT ... FROM ... WHERE ... INNER JOIN in a subquery. Jun 26, 2012 at 16:43
  • Have you tried putting a code-sample (that works in SQL-Server) in Access? Did it give you an error? Jun 26, 2012 at 16:49
  • @ypercube Yes my queries all work in SQL. I can pass the ones that don't use Derived tables over to access just fine but the ones that do use derived tables do not work in ACCESS....
    – Juan Velez
    Jun 26, 2012 at 18:06
  • Well, post an example in the question. The one you have would not work nowhere. Jun 26, 2012 at 18:08

2 Answers 2


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

  • 1
    You are right, it works. But your link provides no example of a derived subquery. Jun 26, 2012 at 17:28
  • yeah the link shows example of subquery and that it exists in access, i dont see any difference when you say derived subquery or just subquery or nested select, they are just used interchangably
    – AmmarR
    Jun 26, 2012 at 17:34
  • 2
    I mean that the examples are only subqueries that don't have / don't need aliases and are not in the FROM clause. Jun 26, 2012 at 17:38
  • 1
    dear @JuanVelez i updated my main post, check it
    – AmmarR
    Jun 26, 2012 at 19:25
  • 1
    @AmmarR Thanks! then there must be something else in my code that is not allowing the query run. I will go over my code again. The sample I gave was just a quick sample. The actual query I am trying to get to work is much longer. As long as it works in Datasheet view then I am happy. Thanks again. I really appreciate it.
    – Juan Velez
    Jun 26, 2012 at 19:25

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:


    MyOtherTable INNER JOIN

    [ SELECT * FROM MyTable WHERE MyID >10 ]. 
        AS MyDerivedTable ON
    MyOtherTable.ID = MyDerivedTable.ID ;
  • @ypercube Is that full stop meant to be there (after the square brackets)?
    – Bridge
    Dec 5, 2013 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.