Combines cells and add object based on contents

[Mwhiterabbbit]

Hello,

I am trying to build a recipe and ingredient database that also can create reports allowing the recipes to be used. However, I cannot make the transition from excel yet as I am running into problems. The appeal of access was that I can use the same form and template for everything and I could consolidate 4 spreadsheets into 1 database but I'm stuck now on what I thought would be the easiest part.

Recipes are broken down into 3 or more steps with different ingredients in each step such like the following

Step          Ingredient           Description
1               100                   Flour
1               300                   Sugar
2               129                   Eggs
3               600                   Chocolate Chips
3               650                   Milk Chocolate chips

Ideally I want to combine the cells that say step 1 and replace with instructions that correspond to those steps. Or find a way to group the steps together so its clear these 5 ingredients are all in step 1. Below the 1's were replaced with Mix 30s High, 2 with mix 10s Low, and 3 with Mix 20s High Mix 10s Low.

Step               Ingredient           Description
Mix 30s High          100                   Flour
                           300                   Sugar
Mix 10s Low           129                   Eggs
Mix 20s High          600                   Chocolate Chips
Mix 10s Low           650                   Milk Chocolate chips

The problem is that I do not know how to make it dynamic. Some recipes have 10 different ingredients in stage 1 while others have only 4. Currently I have 30 different ingredient spots available per recipe, with their item code, description, weight, and stage stored with them. Is there a way to dynamically replace the stage number by consolidating them together then adding the mixing instructions for that stage in their place? This way I can use only 1 report for the recipe when it comes time to retrieve it. Is what I am trying to do feasible? Or will I have to have several different reports for different sized recipes?

[June7]

Database design does not sound normalized. "30 different ingredient spots" seems to indicate 30 fields. Instead of a field, each ingredient should be a record.

Might find this of interest http://access.microsofttemplates.org...-template.html

[Mwhiterabbbit]

Hello,

I have 3 tables set up right now. I have a table for ingredients. Such as their number, name, where to buy, substitute if low, etc. A table for the actual recipe, that has all the ingredients, quantities, its own name, and steps. It could be something like choc fudge dough: simmer 20 lbs chocolate, add 3 lbs milk, sprinkle 6 lbs sugar... Then the last table is for use after its been prepared or more like the finished good or combining recipes together to make something such as add recipes 200, 300, and 500 together to make a pie, or chocolate chip cookies is recipe 210 that has bigger ball sizes and a different bake time than chocolate chip 200.


The blank recipe report has a possible 30 ingredients spaces but if they are blank or empty then they are hidden.

Below is the format I wish to use. I know how to add the space between the two steps but don't know how to automatically make that big procedure cell merge dynamically.

I currently enter in recipes by entering in the step, ingredient #, then the weight on a separate form that is very utilitarian and wish to have them come back in a neater form with extra info from the other two tables. That part is easy but I'm having trouble grouping them together. The only thing I can think of, that I'm trying to avoid, is have 5 different groups of 30 blank spots representing possible steps that each ingredient can be. It would then be filled with VBA and if blank can be hidden or if in the wrong step can be hidden. It would be very cumbersome but i hope there is another way.

[June7]

I am not understanding structure of '30 ingredients spaces'.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Mwhiterabbbit]

hello,

Please see attached DB below. I stripped most of the stuff out so its not that pretty. But you can see in the VBA code me trying to hide the empty ingredients spaces.

The Add new Formula button opens up the entry page or just open FormulaEntering.

FormulaPrint was the form I was working on before I realized it was to be a report. Report1 is the basic template also before I started to ask for help.



Example DB.zip