Take the 2-minute tour ×
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.

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

1 Answer 1

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

 
discard

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.