I'm not even sure where to begin searching for this topic, but I'm certain it can be done and has been done. I just don't know how to implement using Access.
I have one table of 100 items including 2 fields: a primary key and a text field. I have a similar table that has 100 items including 2 fields: a primary key and a text field. What I am trying to create is a table that sits between the middle of these two tables and acts sort of as a relationship manager. The CRM table would have a primary key as well as a two integer fields that would keep a record for the recipe and its corresponding ingredients. For example:
Code:
List 1
1 Chicken Soup
2 Taco
List 2
1 Chicken
2 Broth
3 Vegetables
4 Cheese
5 Taco meat
6 Taco shell
CRM
1 1 1
2 1 2
3 1 3
4 2 3
5 2 4
6 2 5
I'll have a form that prompts for ingredients and then the CRM table would be able to keep a running tab of which recipe needs what. The eventual goal here is to select 3-5 ingredients and then generate a report of what you could make considering those items were in the pantry.
I think you can do this with a query, but I don't even know where to begin to make this as scalable as possible without having to code for each specific scenario of different ingredients. What's the best way to design this?