Show all Cases

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
It is down, each on their own row.

Jerry Whittle said:
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.
Click to expand...
 
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.
 
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,
 
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
Click to expand...

Sounds what like you need is a "Full Outer Join" query. Unfortunately,
Access doesn't support this directly!

What you'll need to do is create two queries - the "Left Join" showing
all Recipe records, and a "Right Join" showing all Purchased. You can
then create a third query using UNION; you need to go to the SQL
window to do so:

SELECT * FROM [Recipe Items]
UNION
SELECT * FROM [Purchased Items];

The UNION query will omit duplicates but will show all records which
occur in either (or both) tables.

John W. Vinson[MVP]
 
Thanks John,

Now that you mention it, I should have thought of that. I guess you just
get stuck in a train of thought and it's tough to switch tracks.

Thanks so much for your help.

Brad

John Vinson said:
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
Click to expand...

Sounds what like you need is a "Full Outer Join" query. Unfortunately,
Access doesn't support this directly!

What you'll need to do is create two queries - the "Left Join" showing
all Recipe records, and a "Right Join" showing all Purchased. You can
then create a third query using UNION; you need to go to the SQL
window to do so:

SELECT * FROM [Recipe Items]
UNION
SELECT * FROM [Purchased Items];

The UNION query will omit duplicates but will show all records which
occur in either (or both) tables.

John W. Vinson[MVP]
Click to expand...
 
Hi David,

That is the next step, you're correct. I think I have that under control, I
just needed to get all the data there first.

Brad
 
Back
Top