is it possible to use a subquery instead of a table in from clause using access?

Question

In MS ACCESS, is it possible to:

use a subquery instead of the first table in a from clause?

(Code below edited with the suggestions by the wonderful people who helped me)

Example:

Part 1 (now working):
    FROM
    (SELECT [Distributor2].PRODUCTCODE
      FROM [Distributor2]

      UNION

     SELECT [DISTRIBUTOR3].PRODUCTCODE
      FROM [DISTRIBUTOR3]

      UNION

     SELECT [DISTRIBUTOR1].PRODUCTCODE
      FROM [DISTRIBUTOR1]
    ) AS [ALLPRODUCTCODES]

INCLUDING PART 2 (now working! Even my multiple join conditions!!!). I've renamed the ALLPRODUCTSCODES table "Table 1" below so it is clearer:

  FROM ((((subqueryabove) AS [TABLE1])
LEFT JOIN [TABLE2] on (Table2.productcode = Table1.productcode AND Table2.year=2013))
LEFT JOIN [TABLE3] on (Table3.productcode = Table1.productcode AND Table3.year=2013))
LEFT JOIN [TABLE4] on (Table4.productcode = Table1.productcode AND Table4.year=2013)

(code edited from first two replies' suggestions - Thank you for your help with MSAccess bracket craziness!!!)

HansUp · Accepted Answer · 2013-11-20T23:01:51.370

3

This query triggers "Syntax error in JOIN operation", which is probably puzzling because there is no explicit JOIN involved.

SELECT sub.*
FROM
    (
        (SELECT 'a' AS fld1 FROM Dual)
        UNION ALL
        (SELECT 'b' AS fld1 FROM Dual)
        UNION ALL
        (SELECT 'c' AS fld1 FROM Dual)
    ) AS sub;

Eliminating the parentheses which enclose each of those unioned SELECT statements, as below, allows the query to run without error. I suspect you may be dealing with the same issue --- so discard those troublesome parentheses in yours.

SELECT sub.*
FROM
    (
        SELECT 'a' AS fld1 FROM Dual
        UNION ALL
        SELECT 'b' AS fld1 FROM Dual
        UNION ALL
        SELECT 'c' AS fld1 FROM Dual
    ) AS sub;

And it can still work when you LEFT JOIN the sub to a table. I created and tested this query in Access 2007 ...

SELECT sub.*, tblFoo.id, tblFoo.some_text
FROM
    (
        SELECT 'a' AS fld1, 1 AS fld2 FROM Dual
        UNION ALL
        SELECT 'b' AS fld1, 2 AS fld2 FROM Dual
        UNION ALL
        SELECT 'c' AS fld1, 3 AS fld2 FROM Dual
    ) AS sub
    LEFT JOIN tblFoo
    ON sub.fld2 = tblFoo.id;

edited Nov 20 '13 at 23:01

answered Nov 20 '13 at 22:11

HansUp

95,961
11
77
135

re: first example - Congratulations! You discovered it, so you get to name it. `INVISIBLE JOIN`? `MYSTERY JOIN`? Let us know what you decide. :) – Gord Thompson Nov 20 '13 at 22:31
1

@GordThompson `JOIN SURPRISE`. @user242379 Your `LEFT JOIN` problem is due to something specific in the query you have *not* shown us. I will add a tested working `(UNION) AS sub LEFT JOIN table` example to the answer. – HansUp Nov 20 '13 at 22:46
Thank you very much for your help HansUp andGord! It's hard to share actual code as it contains fieldnames which contain company names but the elimination of extra parenthesis in the subquery has cleaned up my basic problem. Now I see I have two remaining problems: multiple left joins and multiple conditions in a join (I wish it all just worked like MSSQL!) but I will start a new question if I can't figure these out myself. Thanks again!!!!!!!!!!! – user242379 Nov 21 '13 at 14:14
I think I found the answer!!!! http://nm1m.blogspot.ca/2007/10/multiple-left-joins-in-ms-access.html – user242379 Nov 21 '13 at 14:15

Gord Thompson · Answer 2 · 2013-11-20T22:46:58.787

1

Yes, you certainly can use a subquery in that manner. Your issue is more likely that Access is a bit fussy about parentheses when it comes to multiple joins. Access is prone to complain when it encounters

foo LEFT JOIN bar ON ... LEFT JOIN baz ON ...

It wants to see

(foo LEFT JOIN bar ON ...) LEFT JOIN baz ON ...

Edit:

I see that this question has turned into something of a puzzler. For what it's worth, I just tested this in Access 2010 and it works for me (actual table and field names):

SELECT u.PRODUCTCODE, [TABLE].productdescription
FROM
    (
            SELECT PRODUCTCODE FROM Distributor1
        UNION
            SELECT PRODUCTCODE FROM Distributor2
        UNION
            SELECT PRODUCTCODE FROM Distributor3
    ) AS u
    LEFT JOIN
    [TABLE]
        ON u.PRODUCTCODE = [TABLE].productcode

edited Nov 20 '13 at 22:46

answered Nov 20 '13 at 21:17

Gord Thompson

116,920
32
215
418

I reduced my query just to the select from my subquery and the problem persists so I assume the problem is in the brackets of my subquery. I found this on microsoft's website: [TABLE] query1 UNION [ALL] [TABLE] query2 [UNION [ALL] [TABLE] queryn [ … ]] But I just don't know how to apply it to my subquery.... – user242379 Nov 20 '13 at 21:47
I've updated my code above trying out different things with the brackets. Found this http://stackoverflow.com/questions/4629659/merging-3-tables-queries-using-ms-access-union-query which I tried but still not working (perhaps the link's syntax only works for union all?) – user242379 Nov 20 '13 at 22:11
Thanks to your comments which lead me to believe there were bracket problems, I've also found this which solves the rest of my problem: http://nm1m.blogspot.ca/2007/10/multiple-left-joins-in-ms-access.html I would have had no idea where to start without you. Thank you! – user242379 Nov 21 '13 at 14:16

is it possible to use a subquery instead of a table in from clause using access?

2 Answers2