By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,533 Members | 1,497 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,533 IT Pros & Developers. It's quick & easy.

Join having to match EVERY record on a subset

P: n/a
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!

Jul 10 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
re******@pcdatasheet.com


"Chris L." <di******@uol.com.arwrote in message
news:11**********************@n60g2000hse.googlegr oups.com...
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!

Jul 10 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.