2

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.

0

1 Answer 1

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
1
  • 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, 2012 at 11:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.