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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

How about:

SELECT a.orderid,
FROM   orders AS a
       INNER JOIN (SELECT b.orderid,
                   FROM   orders AS b
                   ) AS c
       ON c.orderid = a.orderid 
WHERE  c.opromisedshipdate = a.opromisedshipdate
share|improve this answer
that wont work because the bopromisedshipdate inside of the inline view destroys the aggregation that we would be doing in there. Sorry the aggregating of the data inside the inline view is not in the code snippet. My bad. – Lumpy Apr 6 '12 at 11:51

Your Answer


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.