473,326 Members | 2,438 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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!

Jul 10 '07 #1
1 1602
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Richard Bolen | last post by:
I'm trying to write a select query that involves 2 tables. One table (Submissions) has a one to many relationship with the other table (Jobs). I'm trying to find all the records in Jobs that do...
1
by: Quarco | last post by:
Hey, Suppose you have a table_ex like: id nuber1 chr ( = a or b) 1 1000 a 2 1001 a 3 1002 b 4 1003 a etc..
9
by: Alan Lane | last post by:
Hello world: Background: Yesterday, January 21, Doug Steele was kind enough to help me out on a Left Join problem. I was trying to return all stores and their Gross Adds for December, 2004...
1
by: Jim | last post by:
I have a form which is based on a join query. The join relationship is one to many. When a new record is added and the user enters a value for one of the fields in the "many" records, I attempt...
3
by: Ben | last post by:
I want to say: SELECT tableA.stuff,tableB.morestuff,tableC.stillmorestuff FROM tableA, LEFT OUTER JOIN tableB ON (AB match conditions) LEFT OUTER JOIN tableC ON (AC match conditions) WHERE etc...
0
by: Vendell | last post by:
Join one of Canada's finest business men... Dear entrepreneur colleague: Here is a message from the founder.... Let me introduce myself. My name is Ariel Topf. I am 42 years old and I have...
2
by: Notgiven | last post by:
I have three tables: table1: table2_ID table3_ID complete table3: table3_ID name
4
by: Jean-Claude | last post by:
Hi, which is the faster query ? (of course, in my case the real queries are more complex) 1/ select * from file1 a join file2 b on b.key=a.key where b.data=123 and b.name='TEST'
7
by: randy1200 | last post by:
I have an orders table. Each record in the orders table contains a customer id. I have a customer table. The primary key of each record in the customer table is the customer id. After getting...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.