Dear Devlin:
Can we begin by specifying a User Interface on which this could run?
I'll suggest you have a multi-select List Box of all the Products, by
ProductName. Hopefully, ProductName is guaranteed to be unique in
your Products table.
From this, a user could choose 5 products, or 3, or 7, or whatever.
From this List Box, the selections can be derived. Iterating through
the selections made, build an IN() clause in the query with the
ProductName(s) in it. Also, you will need the count of how many
Products were selected in that List Box.
The query must filter by these names. It will return the name(s) of
any store(s) that have exactly those selected Products and no others.
This can be done by filtering with the IN() clause (mentioned before)
and by COUNT()ing the rows. Any store that has all the desired
Products will satisfy the IN() clause. Any store that has all the
desired Products and NO OTHERS will also satisfy the COUNT().
The finished query you need to build will look like:
SELECT StoreName
FROM (Stores S
INNER JOIN StoreProducts SP ON SP.SP_R_StoreID = S.StoreID)
INNER JOIN Products P ON P.ProductID = SP.SP_R_ProductID
WHERE P.ProductName IN("ProductName1", "ProductName2")
GROUP BY StoreName
HAVING COUNT(*) = 2
The above contains the details of how to do what you originally asked,
but not all the details of how to implement this through the suggested
user interface. How much other detail you need depends on whether you
want the interface I suggested, and how much you know about
implementing this, or whatever other interface you have in mind.
Come back with specific requests for what details you may need.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
