Unsure if VBA or query is best to use.

[lccrews]

I have a table that contains all ingredients. I have another table that has the formulas for all our recipes. Some ingredients have sub-ingredients though which aren't listed in the formulas. I have a query/form that will pull up the current formula, but it doesn't contain the sub-ingredients. For the reader's essay, ingredients are stored like this: "123456 - SUGAR" and if they have a sub or 10 sub-ingredients, it'll look like this: "123456S1 - SOMETHING".



I'd like to know if it's possible to add a button that, when clicked, will search each ingredient listed in the formula for anything S*. Meaning if "123456 - SUGAR" is listed in the formula, it will search for 123456" to add all related sub-ingredients.

Here's a monkey-wrench for ya... Not all ingredients have exactly 6 digit descriptions. Some have 8.

Thank you for any advice/help.

[June7]

Instead of hyphenated value, would be better as two fields. What is 123456, formula ID?

[lccrews]

123456 is the ingredient designation. 123456 - SUGAR ... It has a unique ID (the primary key)

The reason they are hyphenated is because the information is tied together and used in lookups all over the database. If I parsed them, how could I achieve my goal? Is there a way to accomplish it without parsing them?

[June7]

I am sure there is but I need a better understanding of data structure and relationships.

[lccrews]

Okay, the form I am working on is a formulation form. One that pulls the formulas from tbl_Formulas and combines it with information from tbl_RawMaterial to make a new table tmp_Formula. Raw material is stored in the aforementioned manner due to lookups all over the database needing both parts ("123456" & "SUGAR") together. Currently, sub-ingredients are not listed in tbl_Formulas. The goal here is to automate the identification of the "S*" portion of any ingredient and add it from tbl_RawMaterial to tmp_Formula. Once the process is complete, the formula from tbl_Formulas is replaced with tmp_Formula via a delete query and an append query.

I'm a little confused at what you mean by data structure. Do you want a list of the fields? Relationship-wise, tbl_RawMaterial.RawMaterial = tbl_Formulas.RawMaterial. That's the only relationship in play here.

[lccrews]

I figured it out. Query 1: Raw Material: Left([RawMaterial],6) and Query 2: RawMaterial WHERE RawMaterial=Like [Raw Material] & "*"

I'm sure I can use an unmatched query to append sub-ingredients from query 2 to tmp_formula.

Thanks for the help though June.

[June7]

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