Self-join or Union Query or Other?

[Daryl]

It's been a while since I've used Access and need a little help with querying a table, which is similar to a recipes table. For example, recipe name is peanut butter cookies. Each recipe has a list of ingredients. I want to identify all the ingredients that are common in all peanut butter cookie recipes. If no ingredient is common I would get no result. But if they all have molasses, then I would see molasses as a common ingredient.

The actual table contains the field names company, item, and program. So, for company A, I want to now what item is in all the programs. I've tried find duplicates but that does not work because while I may find the item in 2 programs, that item is not in the third. I need to find that the item exists in all three (or more) programs.

Any help is appreciated.

 

[PNGBill]

How many tables do you have?

Do you have a RecipeTable and IngredientTable or just one RecipteTabel with ingredients as the fields?

 

[Daryl]

Currently there are 2 tables (see attached Excel file). I only used recipes as an analogy (it helps me understand the problem better).
In the Program-Free table (or tab) the Item field is similar to an ingredients field. There are many ingredients that make up the company's product. But what ingredients (items) are common to all products? If there are none common, I imagine I will need to know which ingredient (or item) is most used, so some means of counting would need to be used (and I haven't a clue how to do that either).

Thanks for taking the time to help.

 

[jdraw]

Here's a link to a data model for a recipe database. It may give you some insight on your issue.

http://www.databaseanswers.org/data_models/recipes/index.htm

 

[PNGBill]

I would have a select query return all the items.

A second query would use the first as it's data source and using totals would count the number of records grouping on the "ingredient"
If the first query used criteria "Peanut*" then the 2nd query would only have the ingredients for peanut* recipes and the count result will give you the frequency of each ingredient used.
You can then further criteria the result by >2 to only ingredients that are used in more then one recipe.

The experts may cringe but I would use a few queries to suse out the data and then try and combine some of the tasks and reduce the number of queries. This way I can better follow the steps and see where an error is rather then have complicated problem that I can't get my head around.

I will try and do a sample sql for you if no other support in the meantime - just off for 30 mins.

 

[PNGBill]

I imported the two xl worksheets as tables ProgramData and ProgramFree.

ProgramFree has 8 records with 6 having the same Company field value and the other two unique values ie 8 records with 3 unique values.

ProgramData has 414 records. There is no apparent Primary key, so I added one. Your first field, Co.Prog.ID includes the value 10 yet no such record exists in your ProgramFree tables data.

I will delete this none matching data.

 

[PNGBill]

This sql will count the occurrences of each of the Items and only accept where the number is 8.

SELECT ProgramData.Item, Count(ProgramFree.[Co Prog ID]) AS [CountOfCo Prog ID]
FROM ProgramFree INNER JOIN ProgramData ON ProgramFree.[Co Prog ID] = ProgramData.[Co Prog ID]
GROUP BY ProgramData.Item
HAVING (((Count(ProgramFree.[Co Prog ID]))=8));

If this is the way to go, you would need to build in a way of "8" becoming a variable = to the number of Recipes you have.