Do I need a subquery?

[Guest]

I have a customer orders table (Orders) which contains orders with and
without cost (Orders.Order_Cost).

I need a query to achieve the following logic:

Select all orders for a customer between start date and end date(including
those with no value) only if the customer has a least one order in that
period with value.
Repeat this for all customers.

Do I need to use a subquery? if so any help on query format would be much
appreciated. Thanks

 

[Allen Browne]

You want to list *all* orders for any customer who has an order in a
particular period?

Yes: you need to use a subquery in the WHERE clause to handle the
requirement.

The query statement will be something like this:
SELECT Orders.*
FROM Orders
WHERE EXISTS
(SELECT OrderID
FROM Orders AS Dupe
WHERE Dupe.CustomerID = Orders.CustomerID
AND Dupe.OrderDate Between #1/1/2007# And #1/1/2008#);

For help with subquery basics, see:
http://allenbrowne.com/subquery-01.html

 

[Guest]

Thanks Allen, that's given me something to go on and I will have a look at
your website (it's proved a very helpful source of information in the past).
I am not sure if It will get me exactly what I want as I only need to extract
orders for a customer if the sum value of their orders for the period is > 0,
but I can work on that.

But it's a good starting point.