Late to the party, but another table isn't the way to go AFAIC. You could self join the one table you have
Code:
SELECT tblProdIngredients.Product, tblProdIngredients.Ingredient, tblProdIngredients_1.Ingredient, tblProdIngredients_2.Ingredient
FROM (tblProdIngredients INNER JOIN tblProdIngredients AS tblProdIngredients_1 ON tblProdIngredients.Product = tblProdIngredients_1.Product) INNER JOIN tblProdIngredients AS tblProdIngredients_2 ON tblProdIngredients_1.Product = tblProdIngredients_2.Product
WHERE (((tblProdIngredients.Ingredient)="1") AND ((tblProdIngredients_1.Ingredient)="3") AND ((tblProdIngredients_2.Ingredient)="4"));
It may not be a slick as june7's though, which I suppose isn't entirely dynamic either since the IN portion of the clause would have to be modified for variations, same as having to add another instance of a table. The table for the above looks like
Product |
Ingredient |
Product1 |
1 |
Product1 |
2 |
Product1 |
3 |
Product1 |
4 |
Product2 |
1 |
Product2 |
3 |
Product2 |
4 |
Product3 |
1 |
Product3 |
3 |
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.