Microsoft Access constrain view by date in outer query I'm working on a query in Microsoft access and it does not appear to allow me to filter the inline view by a date that exists in the table outside of the inline view.    A simplified version that exibits the problem is below

select a.orderid, a.ocustomerid
from orders AS a
         INNER join (
SELECT b.orderid, b.ocustomerid 
FROM orders AS b 
WHERE b.opromisedshipdate = a.opromisedshipdate 
) AS c ON c.orderid = a.orderid


Access returns an error looking for the parameter a.opromisedshipdate. 

Any idea how I can achieve this filter?  

In the real world query I'm doing this to provide aggregated order information (the inline view) next to non aggregated order information but I need the aggregated information to only go up to the date that is a part of the current order.

Thanks in advance.
How about:

SELECT a.orderid,
       a.ocustomerid
FROM   orders AS a
       INNER JOIN (SELECT b.orderid,
                          b.ocustomerid
                          b.opromisedshipdate
                   FROM   orders AS b
                   ) AS c
       ON c.orderid = a.orderid 
WHERE  c.opromisedshipdate = a.opromisedshipdate