Query condition

[Robin]

Hi All,
I have two tables for managing our sales orders. One has the order header
info such as customer name, account number, etc and the other stores the
order line info such as productID, unit price, qnty, etc. The tables have a
one to many relationship and are joined using the OrderID.

I need to create a query to list all the sales orders that include both
productID 57 and 58 but i can't quite get my head round it! If i put "57 or
58" in the criteria box it gives me all of the orders that include either 57
or 58 whereas i need to know the orders that include both!

Thx in advance.
Rob

 

[Ken Snell \(MVP\)]

You need a subquery that finds all the records in order line info table with
the appropriate productid values, then use that to filter the main query:

SELECT OrdersTable.*
FROM Orders
WHERE Orders.OrderID IN
(SELECT T.OrderID
FROM OrderLineInforTable AS T
WHERE T.ProductID = 57 OR
T.ProductID = 58);

 

[Allen Browne]

Because you need to query 2 different *rows* of data, you could use a
subquery to choose the other one.

This kind of thing:

SELECT OrderDetail.*
FROM OrderDetail
WHERE (OrderDetail.ProductID = 57)
AND EXISTS (SELECT OrderID
FROM OrderDetail AS Dupe
WHERE Dupe.OrderID = OrderDetail.OrderID
AND Dupe.ProductID = 58);

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

 

[John Spencer]

If your order details table never duplicates the product id for any one
order then you could use the following

SELECT Orders.*
FROM Orders
WHERE Orders.OrderID IN
(SELECT OrderDetails.OrderID
FROM OrderDetails
WHERE OrderDetails.ProductID IN (57,58)
GROUP BY OrderID
HAVING COUNT(OrdersID) =2)

If you could duplicate you can use the query suggested by Allen Browne or
you could use the following assuming that your field and table names DO NOT
require brackets


SELECT Orders.*
FROM Orders
WHERE Orders.OrderID IN
(SELECT A.OrderID
FROM (SELECT DISTINCT OrderID, ProductID
FROM OrderDetails
WHERE ProductID IN (57,58)) as A
GROUP BY A.OrderID
HAVING COUNT(A.OrderID) =2)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Robin]

Thnx guys, i'll give it a go!

Because you need to query 2 different *rows* of data, you could use a
subquery to choose the other one.

This kind of thing:

SELECT OrderDetail.*
FROM OrderDetail
WHERE (OrderDetail.ProductID = 57)
AND EXISTS (SELECT OrderID
FROM OrderDetail AS Dupe
WHERE Dupe.OrderID = OrderDetail.OrderID
AND Dupe.ProductID = 58);

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

 

[Robin]

Hi Allen,
I tried your suggestion and i've got a result but the query is only showing
productID 57. Is this correct or should both productID's be listed?
Robin

 

[Allen Browne]

The way the query is formatted, it will show only the 57 product unless you
adjust it further (e.g. grouping on the OrderID field, and using Where in
the Total row under the ProductID.)

John Spencer also posted a good reply. It's worth experimenting with his
suggestion too.

 

[Ken Snell \(MVP\)]

Robin -

My suggestion won't work for returning the results you want -- I misread
your original post, sorry!

 

[Robin]

Allen,
I've been using the sub query you suggested and it works well. However,
i've realised that it would be beneficial to show all of the order lines that
belong to the order. You mentioned in your last post that i should use a
grouping on the orderID? Can you explain this in a bit more detail?
Thanks
Robin

 

[Allen Browne]

Grouping on the OrderID (only) would not give you the detail lines.

To create a query that groups records, depress the Total icon on the toolbar
(in query design.)