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!!!)

Answer 1

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;