Union Query Help

[Guest]

SELECT Ingredient,MenuItem, NumberofOunces
FROM tblIngredients
UNION SELECT ItemName, Ingredients, NumberofOunces
FROM tblInHouseReceipt;
This is a copy of my Union Query. I have Ingredient that are made from
scratch. out sauce is made in house by when I put it in the ingredients table
I called it sauce. In my in house rec. I listed the ingredients that go into
the sauce. Now I want to join the two tables so I i can cost out the sauce
with in my ingredients table. This query gives me all the reocrd and I only
want those that match.
Please Help

 

[Jeff Boyce]

Lisa

"that match" ... how? on what are you matching.

The UNION query you provided puts ItemName and Ingredient in the same
column, and MenuItem and Ingredients in the same column. These don't seem
like the same things.

What is the relationship between the tblInHouseReceipt and the
tblIngredients? Is this a one-to-many relationship?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

SELECT Ingredient,MenuItem, NumberofOunces
FROM tblIngredients
UNION SELECT ItemName, Ingredients, NumberofOunces
FROM tblInHouseReceipt;
This is a copy of my Union Query. I have Ingredient that are made from
scratch. out sauce is made in house by when I put it in the ingredients table
I called it sauce. In my in house rec. I listed the ingredients that go into
the sauce. Now I want to join the two tables so I i can cost out the sauce
with in my ingredients table. This query gives me all the reocrd and I only
want those that match.
Please Help

That's what a UNION query is designed to do - return all the records in the
first SELECT, all the records from the second SELECT, remove the duplicates
and display all the unique records. I'm pretty sure you want a simple
two-table JOIN instead.

I have no idea why you have a NumberOfOunces in both tables, nor which other
field is matching in both tables - is Ingredient the same as ItemName, or as
Ingredients? or neither?

John W. Vinson [MVP]