If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Looking up orders with specific combinations of products
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 |
#2
|
|||
|
|||
Looking up orders with specific combinations of products
"JJP" wrote in message
... 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') ) ) -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#3
|
|||
|
|||
Looking up orders with specific combinations of products
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 JJP wrote: 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 |
Thread Tools | |
Display Modes | |
|
|