Select customers who've purchased two types of product in same order?

[AndyHorn]

Hi,

I'm struggling to write a query that will return all customers that have purchased product A and product B in the same order.

Any suggestions how I might acheive this?

Thanks

 

[fneily]

With the very little information you gave, here's is what your tables should look like. tblCustomers, tblProducts, tblOrder.
tblOrder will look something like:
OrderID
OrderDate
CustomerID from tblcustomers
ProductID from tblProduct
Quantity

The primary key for tblOrder will be a multi-field, OrderID/OrderDate/CustomerID/ProductID

So, if you need to see products bought by a customer for a certain order, this'll work based on the OrderID.
But what if the customer comes back on the same day and places another order and you want to see what he bought that day? Well, this'll work too because of the Orderdate field.
Want to see all products bought by the customer? No problem.
How about which customers bought a certain product? No problem.
Which products aren't selling? No problem.

Seems to work.

 

[dhookom]

Here is the query that should work in the Northwind sample MDB finding orders/customers where the products 41 and 57 were purchased in the same order:

SELECT Customers.CompanyName, Orders.*
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE (((Orders.OrderID) In (SELECT OrderID FROM [Order Details] WHERE ProductID IN (41,57) GROUP BY OrderID HAVING Count(*) =2)));

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]