Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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. –  JuanVelez Jun 26 '12 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. –  ypercube Jun 26 '12 at 16:43
    
Have you tried putting a code-sample (that works in SQL-Server) in Access? Did it give you an error? –  ypercube Jun 26 '12 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.... –  JuanVelez Jun 26 '12 at 18:06
    
Well, post an example in the question. The one you have would not work nowhere. –  ypercube Jun 26 '12 at 18:08

2 Answers 2

up vote 3 down vote accepted

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

share|improve this answer
1  
You are right, it works. But your link provides no example of a derived subquery. –  ypercube Jun 26 '12 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 '12 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. –  ypercube Jun 26 '12 at 17:38
1  
dear @JuanVelez i updated my main post, check it –  AmmarR Jun 26 '12 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. –  JuanVelez Jun 26 '12 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:

SELECT * 
    FROM 

    MyOtherTable INNER JOIN

    [ SELECT * FROM MyTable WHERE MyID >10 ]. 
        AS MyDerivedTable ON
    MyOtherTable.ID = MyDerivedTable.ID ;
share|improve this answer
    
thanks appreciate it! –  JuanVelez Jun 26 '12 at 19:28
    
@ypercube Is that full stop meant to be there (after the square brackets)? –  Bridge Dec 5 '13 at 14:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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