Join having to match EVERY record on a subset

Imagine a situation where a customer buys products and is awarded free
articles if s/he buys predefined sets of products.

For example if s/he buys twenty articles, among them a "cereal bowl",
a "coffee mug" and a "orange juice bottle", then s/he bought a
"breakfast set" and is awarded a free toaster.

I need to store the "cereal", "coffe" and "orange" items on the
"rules" table. (There will be other composite rules as well --buying
"soap", "sponge", "shampoo" and "towel" would mean the customer
purchased a "shower set" and is awarded some other freebie).

Next I have to match the items on the customer's shopping cart, to the
"rules" table. The catch is that for a rule to match, EVERY one of its
records must join. (S/he must have bought all items (at least one of
each) to qualify and "match" the set of rules). I thought of using
count(*) to check # of items bought against number of rule parts, but
if the purchase includes just two "coffees" and one "cereal" I was
getting false positives.

No need to worry about matching more than one rule.

Any ideas on how to accomplish this?

Any pointers immensely appreciated! Thanks in advance!

re: Join having to match EVERY record on a subset

How about ---
TblArticle
ArticleID
Article

TblShoppingCart
ShoppingCartID
CustomerID
ShoppingCartDate

TblShoppingCartArticle
ShoppingCartArticleID
ShoppingCartID
ArticleID

TblRulePrize
RulePrizeID
RulePrize

TblRule
RuleID
RuleName
RulePrizeID

TblRuleArticle
RuleArticleID
RuleID
ArticleID

Per your example --
TblArticle
1 Cereal Bowl
2 Coffee Mug
3 Orange Juice Bottle

TblRulePrize
1 Toaster

TblRule
1 Breakfast Set 1

TblRuleArticle
1 1 1
2 1 2
3 1 3

You now need a QueryDef based on TblShoppingCartArticle. You
programattically set the criteria for ArticleID to the ArticleIDs for RuleID
= 1 in TblRuleArticle. The criteria would look like 1 And 2 And 3. You then
use DCount to determine if the query returns a record. If Yes, the shopping
cart wins the RulePrizeID in TblRule. If No, ypu programatically do the same
for RuleID = 2 in TblRuleArticle. You just need a loop to go through the
rules.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
resource@pcdatasheet.com






"Chris L." <diversos@uol.com.arwrote in message
news:1184098206.741814.149700@n60g2000hse.googlegr oups.com...