Best way of doing this?..

[resolva]

Hi all,

Basically I have two tables, one being "new project" and the other being "recipes" which need to be added to a project.

For example you will have project 1 and recipe 1,2,3,4,5 for this project then project 2 and recipe 1,2,3,4,5 for this project.

What would be the best way of going about making it so once a project is entered, I could then add recipes to the particular project.

For example id like to make it able, so the user will create the project and title it say "project one" then will click "add recipes" and then will be able to add recipes to that project.

I would of posted this in the forum but I have just created a new thread and didn't wish to fill the forum up with my own question.

Thank you in advance

 

[MarkK]

What you describe is the textbook Many-To-Many relationship, where one project may contain many recipes, and one recipe might be a member of many projects. This is always a three table solution where the linking or joining table might be called ProjectRecipe.
In it's minimal form this table has three fields...
1) it's own unique ID
2) the project ID
3) the recipe ID
Your other two tables each have a One-To-Many relationship with this intermediate table.
Then you can design a ContinuousForm or Datasheet subform around this middle table. As a subform of the Project form, you use a two column ComboBox on the RecipeID field such that the RecipeID is the (hidden) bound column, and the recipe name is visible. Link the subform on ProjectID and *poof* your users simply select records from this ComboBox to add Recipes to the project.
Likewise, your Recipe form can very easily display which projects each recipe is a member of, and that data could as easily be editted in that scenario.

 

[resolva]

Amazing, thank you for such a detailed reply!

So far I have put together three tables, Project Table, Recipe Table and the Intermediate table like you said and linked them all together.

I will try the combo box tomorrow and see how it goes. Will reply again if I have another problem.

Thank you again

 

[resolva]

I have attached a jpeg of the current relationship I have setup on the database.

Is this the best/right way of doing it?

Thank you