Subtable of Two Tables

[swalsh84]

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:

 
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?

[TheShabz]

i wish there was a multi-select list box to make life a bit easier but....

your CRM should be accomodating a many to many relationship between List1 and List2. preset your List tables with all possible options.

This part is off the top of my head and is probably really kludgy but here goes:

On your form, have a combobox with the meals, and a bunch of checkboxes with each ingredient. your "run" button will first count how many checkboxes there are. Once you have this count, create a For Loop that will run an INSERT INTO statement into your CRM table, cycling through the value for each checkbox, and loop until its counter hits the count of checkboxes counted earlier.

If you get the logic there, the code shouldnt be too hard to come up with. Hope it helps

[swalsh84]

I thought about that, but list 2 has a very large number of ingredients (about 100 last time I checked). It would be a bit cumbersome to do it that way. It's great idea for small scale application, but I don't think it's the best solution here.

Is there another way to do this? Ideally this will roll out to users that know nothing of database design and it would be helpful to make it as friendly as possible.

Thanks for the suggestion!

[TheShabz]

The only other way I can think of would be to enter it in one ingredient at a time. You can make a textbox that is basically a search box. In it's On Change event, you run a query that displays its results in a listbox below it, using
WHERE ingredient Like (me.txtbox & "*").

Outside of that, I got nothing.