Some Ideas on Tables my data base has got very messy and I dont think my tables are r

[Wornout]

I have built 3 data bases now and still think there is another way can someone come up with a better Idea.
I have A) Base recipe B)Base Recipe name C)Ingredients D)Recipe E) Recipe Ingredients Both D and E are for variants on the base recipe so they add the base recipe and ingredients and then the variant name and ingredients. and a unit of measure. The data base has got messy with union queries and forms etc. All because we have to bring up a base recipe and add a variant name and ingredients and save it under the variant name separately from the base name IE Bring up Mince and all it ingredients then add a new name Mince and cheese and its extra ingredients. Then the reports all have extensive calculations in it.

[zmbd]

It sounds like you've made a good attempt at normalization; however, needs to be tweaked a tad. If you don't know what I mean by normalization then please read here: > Database Normalization and Table Structures.

Before we re-invent the wheel, have you taken a look at the recipe database templates circulating on the web such as this one from the MS templates: MS - Recipe collection database

Even if this doesn't quite match what you are after, then it might provide some insight on how to organize everything.

As for the table structure... I can see an argument for having a table that has the main receipe and then an alterations table... Personally, I would more than likely go with:
[tbl_mealclass](snack, dinner, supper, potluck); [tbl_meal](mince, mince with cheese - linked to tbl_class); [tbl_ingredients](flour, milk, eggs, etc); [tbl_ recipeingredien ts](linked to tbl_meal with a list of the ingredents and how much of each for a given meal. Yes it would have entries for both “mince” and “mince with cheese;” however, the data entry form for a new recipe can be designed to pull the base recipe ingredients and allow their modification, addition to, and removal from the new recipe.); and finally [tbl_ recipedirection s](also linked to tbl_meal, this would have the "add this to that and bake... might have some sub-classifications such as prep, main, etc.. which would require another table)

[Wornout]

Great thanks, The recipes data base you directed me to was the one I used to go by in the beginning. I have carried on with the one I have got and done a lot of VBA coding to simplify for the end user.
I do have another question am not sure if I have to ask separately or because it is on the same DB just ask here
My sub form I want to link master and child can I use an if statement? where would I put it and how would I word/code it. I want to say IIF [Flavor]is Null return new rec or if there is a flavor in the box return ingredients matching that flavor
Flavor is in the master form and is a bound txt box
I want to be able to add to it or change the ingredients.

[zmbd]

I was afraid of that... However, it never hurts to look at the templates for insperation (^-^)

The subform question should go in a new thread; however, normally there is a single related field between the main and subform(s). How you would pull the record set would depend on the table design.

[Wornout]

Why would you be afraid of that? was that data base not to good?
Thanks for your answer :-) Walked away for a couple of hours and am now trying again

[zmbd]

"afraid" in the sense that you'd alread looked at the template and found it lacking the features you need.