Query condition

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

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

--

Ken Snell
MS ACCESS MVP


"Robin" wrote in message
...
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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robin" wrote in message
...
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

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" wrote in message
...
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

Thnx guys, i'll give it a go!

"Allen Browne" wrote:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robin" wrote in message
...
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

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" wrote:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robin" wrote in message
...
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

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robin" wrote in message
...
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" wrote:

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" wrote in message
...
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

Robin -

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

--

Ken Snell
MS ACCESS MVP


"Ken Snell (MVP)" wrote in message
...
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);

--

Ken Snell
MS ACCESS MVP


"Robin" wrote in message
...
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

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" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robin" wrote in message
...
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" wrote:

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" wrote in message
...
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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robin" wrote in message
...
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" wrote:

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.

"Robin" wrote in message
...
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" wrote:

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" wrote in message
...
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