Looking up orders with specific combinations of products

[JJP]

Our database is not relational. Each order number is repeated for each and
every line item so if an order has ten different products there are at least
10 rows. Sometimes I need to find order that contain specific combinations of
products. For example I might need to find order that contain products B and
(F or Y). So far I have done this by creating a query that looks for orders
with product B, then a Querry that looks for orders with F or Y, then a query
that links the two previous queries and finally another query that lets me
see the entire value of the orders that contained B and (F or Y) plus that
value of anyother products in that order.

It does not work very well and is cumbersome. Is there a better way?

Thanks,
John

 

[Dirk Goldgar]

Our database is not relational. Each order number is repeated for each and
every line item so if an order has ten different products there are at
least
10 rows. Sometimes I need to find order that contain specific combinations
of
products. For example I might need to find order that contain products B
and
(F or Y). So far I have done this by creating a query that looks for
orders
with product B, then a Querry that looks for orders with F or Y, then a
query
that links the two previous queries and finally another query that lets me
see the entire value of the orders that contained B and (F or Y) plus that
value of anyother products in that order.

It does not work very well and is cumbersome. Is there a better way?

Thanks,
John

A non-normalized table like that makes it a bit harder to query, but
something along these lines should work:

SELECT * FROM tblOrders WHERE
(
EXISTS
(
SELECT T.OrderID FROM tblOrders T
WHERE T.OrderID = tblOrders.OrderID
AND T.ProductID = 'B'
)
AND
EXISTS
(
SELECT T.OrderID FROM tblOrders T
WHERE T.OrderID = tblOrders.OrderID
AND T.ProductID In ('F', 'Y')
)
)

 

[John Spencer MVP]

IF an order never has a repeat of the product - two lines with "F" for
instance then you can use a query that looks like the following.

SELECT *
FROM TheTable
WHERE TheTable.OrderNumber in
(SELECT OrderNumber
FROM TheTable
WHERE Product in ("F","Y")
GROUP BY OrderNumber
HAVING Count(OrderNumber) =2)

You can use the same concept if you are allowed to duplicate products, but you
need to use a subquery in a subquery.

SELECT *
FROM TheTable
WHERE TheTable.OrderNumber in
(SELECT OrderNumber
FROM (SELECT DISTINCT OrderNumber, Product FROM TheTable) as Temp
WHERE Product in ("F","Y")
GROUP BY OrderNumber
HAVING Count(OrderNumber) =2)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County