Using Query Results as a Criteria for Another Query

[trussell]

Hi everyone,



Is it possible to use the output of another query as the criteria for a different query? To be specific, I have two queries. In Query 1 I have a criteria for a client name (the user inputs the client name they are interested in), which spits out a table that includes the client name and corresponding "Order ID". In query 2, it would need to look at the output from Query 1 and, using the remaining "Order ID's", apply an additional filter based on a date range specified by the user.

How would I go about implementing this?
Any advice would be appreciated. Thanks!

[Micron]

I think what you're asking about is known as a stacked query where a query is used to provide a 'filtered' recordset to a 2nd query. You add the query to the 2nd query as you would a table. Why wouldn't you just use the date criteria in the first query as well as the client id?

[kd2017]

I'm not really sure what your situation is but I imagine a query like this

SELECT * 
FROM   order_table 
WHERE  order_table.order_date BETWEEN [ENTER STARTING DATE] AND [ENTER ENDING DATE] 
       AND order_table.customer_id = (SELECT TOP 1 customer_table.customer_id 
                                      FROM   customer_table 
                                      WHERE  customer_table.customer_name = [ENTER CUSTOMER NAME]);

[CJ_London]

don't see why you cant just have an ordinary query with two criteria. Not clear what you mean by remaining OrderID's - all of them? just for this customer?


Assuming the latter and amending kd's code

SELECT * 

FROM   order_table LEFT JOIN Customer_table ON Order_table.customer_id=customer_table.customer_id

WHERE  order_table.order_date BETWEEN [ENTER STARTING DATE] AND [ENTER ENDING DATE] 
       AND order_table.customer_name = [ENTER CUSTOMER NAME]
       AND order_table.order_id<>customer_table.order_id