RE: Show all Cases

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

There are 3 cases.

Think of it like a cake recipe.
The chef has the recipe that has 3 things on it to make the cake.
3 scenarios, and in each scenario I need to see all 3 things on the recipe.

Scenario 1: buy exactly what is on the recipe
Recipe: Purchased:
Eggs Eggs
Milk Milk
Flour Flour

Scenario 2: buy 2 of the 3 items
Recipe: Purchased:
Eggs Eggs
Milk
Flour Flour

Scenario 3: buy an extra item (4 things)
Recipe: Purchased:
Eggs Eggs
Milk Milk
Flour Flour
Vanila

I need the query to work in all cases. So regardless of the scenario, I need
to see all things purchased and all things on the recipe, regardless of what
recipe I'm using.

If I join on purchased, then I will exclude records where the recipe does
not match the purchsed items, Scenario 2 and 3. If I say show all Recipe
items and only the Purchased where they are equil, then I miss the extra Item
Purchased in Scenario 3. If I say show all Purchased Items and only the
Recipe items where equil, then I will miss a Recipe item in Scenario 2

I need all Recipe items and all Purchased Items with out seeing all
purchases for every recipe item. So 4 records, not 4 x 3 = 12 records.

Thanks,

"Jerry Whittle" wrote:

Excellent. Now what exactly do you want to see returned in the query? I
notice that Salt is in one table but not the other and I bet that's part of
the problem.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Brad" wrote:

It is down, each on their own row.

"Jerry Whittle" wrote:

Brad,

I'm confused by your examples. Do the records look something like:

Ingred Flour Sugar Egg Milk Salt
or
Ingred Flour
Ingred Sugar
Ingred Egg
Ingred Milk
Ingred Salt

If it's like the bottom example, you work is cut out for you but can be done.

If the data is across like the top, you're doomed.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Brad" wrote:

Thanks for taking the time to read my question.

I have 2 tables. Table 1 is what the customer purchased, and Table 2 is what
the customer should purchase.

Example (same as Case 2)
Table 1 (Actually purchased)
Ingred
Flour
Sugar
Egg
Milk

Table 2 (Supposed to purchase)
Ingred
Flour
Sugar
Egg
Milk
Salt

What I need to return is all of Table 2 every time and all of Table 1 every
time, but I don't want to see every record by every record. So if I join on
Ingred I limit one side or the other depending on my join (1,2 or 3).

How can I do this?

Cases:

1 Customer purchases everything on the list that they should get
2 Customer doesn't purchase everything on the list that they should get
3 Customer purchases more than what is on the list that they should get

Thanks,

Brad
.

Relevant Pages

  • Re: Haris Pilton - be careful
    ... so these vendors should too. ... If one were to purchase ... Thelsamar due to server lag. ... So on a whim I listed the 4s recipe on the AH ...
    (alt.games.warcraft)
  • Re: half sour pickles
    ... I cant find a recipe for half sour pickles. ... All the recipes I find for pickles have vinegar. ... The ones I purchase do not have any vinegar. ... Any help in finding a suitable recipe would be appreciated. ...
    (rec.food.cooking)
  • Re: half sour pickles
    ... Mitch Scherer wrote: ... I purchase them at the Shoprite supermarket. ... Any help in finding a suitable recipe would be appreciated. ... I bought a jar of half sour pickles after the last discussion of it in this group a month or so ago. ...
    (rec.food.cooking)
  • Re: half sour pickles
    ... I cant find a recipe for half sour pickles. ... All the recipes I find for pickles have vinegar. ... The ones I purchase do not have any vinegar. ... Any help in finding a suitable recipe would be appreciated. ...
    (rec.food.cooking)
  • Re: Show all Cases
    ... If I say show all Recipe ... items and only the Purchased where they are equil, then I miss the extra Item ... Purchased in Scenario 3. ... Sounds what like you need is a "Full Outer Join" query. ...
    (microsoft.public.access.queries)