"Can't find table" error in SQL FROM(UNION) AS table, INNER JOIN, COUNT() FROM table

I currently have a query where inside each union select, in order to get a count of occurences, I had something like:

SELECT Order_Id, Order_Date, 
       C.cnt AS Order_Parts
  FROM table1
  INNER JOIN (SELECT Order_Id, count(Order_Id) as cnt
                 FROM table1
                GROUP BY Order_Id) C ON table1.Order_Id = C.Order_Id
UNION SELECT Order_Id, Order_Date, 
       C.cnt AS Order_Parts
  FROM table2
  INNER JOIN (SELECT Order_Id, count(Order_Id) as cnt
                 FROM table2
                GROUP BY Order_Id) C ON table2.Order_Id = C.Order_Id

And it worked alright, but I'm reorganising it so that the UNION is inside the Query FROM, so something like this:

SELECT
Order_Id,Order_Date,C.cnt AS Order_Parts

FROM(
    SELECT Order_Id, Order_Date
    FROM table1
    UNION SELECT Order_Id, Order_Date
    FROM table2
) AS Parts
    INNER JOIN (SELECT Order_Id, count(Order_Id) as cnt
                FROM Parts
                GROUP BY Order_Id) C ON Parts.Order_Id = C.Order_Id

But Access throws me an error saying it can't find table or query 'Parts'. I can't for the life of me figure out why it can't use it; could someone guide me to what's wrong?

Solution

The error seems pretty clear. A table alias (i.e. parts) refers to a table or reference for the purpose of defining columns. It does provide a new "source" for data.

That is, it cannot be reused as a table in the from clause.

This is true in all databases. However, just about any other database supports common table expressions -- which do what you want. MS Access does not. Your only options are:

Repeat the UNION query for the second reference.
Use a view.

Note: There might be other approaches to writing the query that you want to write. For that, I would suggest that you ask a new question with sample data, desired results, and a clear explanation of what you want to accomplish.

Also, you may not want UNION because that removes duplicates. UNION ALL is more common.