Approaches to using saved queries for doing calculations on forms

[patapotato]

OK, I made a sample file for those Excel tables. I'd say about 80% of all recipes can be converted to be somewhat in line with this layout. Sometimes there is an additional misc calc table, sometimes what's calculated in there is different and sometimes there are extra or omitted elements in some of the other sections. But for the most part it can be arranged somewhat like this. The formulas are obviously nonsense. I was thinking of dividing the thing up in like 7 sections. One header for all the basic info (product name, recipe name, batch nr, start, reactor nr, operator info + inportant info). Then one section for what is called Masterbatch nr I guess, then one for the ingredient calculations, two optional misc tables (one present here), one section that has all the instructions for each production step, as well as the input fields, and then one which has the misc stuff at the end (cooling, lab report, transport to tank).

I also have a preliminary database in which I was playing around with storing formulas in queries. It's really just me trying to figure out how things can be done so the tables and fields don't align 100% with the excel file in the sample. I am also not happy with the tables really. There are Var1,2,3 for example, in two different tables, which really should have different names since they represent different things. I also don't thinkI should store the names of the forms and subforms in the recipe table but rather in the query table so that every query name has the corresponding form name that uses said query in the same row instead. But there is a lot that's not optimal yet I suppose, it's a really quick sketch of an idea more or less. It functions semi well imo at the moment. I have not yet thought about how to store user input and how to print out a report etc. I also think that maybe it would be wise to not store any input fields in the tables at all, but rather create them with the queries. They should best not store the input values permanently to the tables since they need to be re-entered every batch. Still a lot of questions there.

I'm happy with any feedback or ideas really. I'm still in the early concept stages.
BTW, there are a lot of functions and some modules that have little to do with the project atm. I just though I might need them when I started but really did't use them for anything meaningful. There are also a few DLookup calls, but I guess they can easily be removed by adding additional controls to the forms.

 

[Pat Hartman]

Start by taking off your Spreadsheet hat. Your schema does NOT need to look like the spreadsheet, nor should it.
The section that lists the chemicals is the ingredients part of the recipe.
The bottom section then takes each chemical, in order and explains the processing around its being added to the mix. Go back and look at my numbered list and see how these two separate sets of data get combined to product the instructions in order.

The instructions are printed out in a grid. Notice how the ingredients are merged into the text and prefixed by a #. The individual weights are listed and next to them the cumulative totals. Then the next bunch of columns are actuals. As I said, we ended up collecting these on the printed documents but ONLY because the client didn't want to take the time to convert the "operation" text to boiler plate with tokens for variables. I included the report design view and also a screen image of the weightmaster which showed just the ingredients in the order in which they are added to the batch, including the path since not all ingredients came from the same source. You can see the effect of the tare points if you look closely at the cumulative weight and can understand how the ingredients come from different paths into the mixer. I did this a very long time ago and the details are a little blurry so don't as questions that are too technical?)

It is VERY important to normalize the tables. any time you have column names with common prefixes or numeric suffixes, they are suspect and those groups of items should almost always be separate tables. Notice how each block in the BCR has only ONE ingredient. In the places where it looks like you need multiples, you probably are better off separating the instructions so that they say the same thing for three ingredients in a row rather than trying to embed three variables. It also means that the weight an running totals will be cleaner.

 

[patapotato]

Thx for the info!

A couple of questions:

First, regarding the Weightmaster, I think you mentioned you just use a factor to multiply with to get the right weight for a given batch size. Like 120 (kg?) in the sample. So like 120 *0,4858=24,7 for Item 0211H for example? Obviously there are some unit conversions but in my case it's all in kg anyway. Where do you store those ratios? They have to be different between ingredients, but also between recipes that hold those ingredients, so I guess in a junction table? Or have you made an extra indicator unique for every combination of ingredient and recipe and a seperate table for that?

The formulas I need to work with are a bit more complicated than that unfortunately. There are often multiple factors, some of which need to be manually entered by an operator (certain chem characteristics change depending on from where an ingredient was sourced or how pure it is; those can vary from day to day and are determined by the lab before production). Depending on those measures the ratio of a certain ingredient will then be different and because the value of this ingredient is different, one of the other ingredient calculation might also depend on that ratio itself. Many recipes also require a secondary or tertiary table for calculating certain variables. I was initially thinking of just converting those to a single simplified expression and run that in one go to just get the end result, but aparently the operator needs to have the info on all those inbetween measures when they prepare to run the batch.

Second, regarding the Detail section of the BCR, I don't think I know how this was done. There is a control (I am guessing a textbox) and it contains an expression. How exactly do you get the grid view from that? First I thought it was just the string expression for merging the ingredient name with the instruction text etc, but that does not seem to be right. You also need the calculated weights and somehow add the grid. Do the [D1Text] things correspond to the first five cells in each row of the detail section or how exactly does this work? The text for Operation would then already be merged as [D1Text] using some seperate routine? Are you using a continuous form for that?

 

[Pat Hartman]

I built this a very long time ago so the details are fuzzy. I was reading through some old documentation and it appears that the first and last item are a pair. Don't ask me why the IDs are different but the first is 83% and the last is 17% and that = 100% and makes the numbers add up. This was the only picture I had so I posted what I could find. There was probably an intermediate step. I would have to go back and find a table with the "recipe" in it. Let's assume the total weight is 100 so we don't have to deal with multiplication. There are 5 ingredients. Each representing a percentage of the total weight. The third column = kg.

Item, %, Running weight
Item1, 4%, 4
Item2, 6%, 10
Item3, 15%, 25
Item4, 25%, 50
Item5, 50%, 100

If we change the batch weight to 200 then we have
Item, %, Running weight
Item1, 4%, 8
Item2, 6%, 20
Item3, 15%, 50
Item4, 25%, 100
Item5, 50%, 200

At it's simplest, that's how it worked. The weights for which the formula was valid were listed. You couldn't just plug in 150 if no one had validated the formula for that batch weight.

If you want to go with specific weights rather than a percentage, you can do that. You just pick a minimum weight size and that is the base. So if we stick with 100 for simplicity, then if you wanted a batch of 200 kb, then the multiplier would be 2. If you wanted 500, the multiplier would be 5. It changes the calculation but not the principle

If the actual weight of ingredients could vary by batch, then you can't give the operator a weighmaster to match to since today, Item5 might weigh 47.8 instead of 50 (in the 100 kb batch). You might be able to provide a valid range and use that to say that the total after adding Item5 is 97.8 instead of 100 but that falls within the 97.7 and 100.8 range. I would need to spend hours, days, with your data to completely understand it. From where I sit, I can only tell you what might work. Also, making use of the Tare feature of the scale can resolve problems like this.

THE MOST IMPORTANT POINT- make the data drive the application. You don't want hard coded word or excel files. They are way too hard to modify going forward. Work very hard to normalize the data so you can come down to ONE ingredient per operation. That will enable you to produce a document where you can list each ingredient next to its step with its weight and the running weight of the batch. You can keep theoretical and actual numbers. Not all steps have ingredients. You can either try to embed variables into the instruction text as you see in my example or you can write simple sentences with separate columns for things like RPM, pH, temperature, time. I think those were the only four variables I had. To simplify the logic don't hard code anything. Make the variables a child table. One row per variable so it doesn't matter how many you have. At each step, run a loop that reads the variables (it doesn't matter if there are none) and Replace("yourfield", rst.VarName, rst.VarValue). If there's none, there's none. If there's 50, there's 50. It is irrelevant to your code. The loop runs once for each row in the child table against the same "yourfield". To facilitate testing, you can count the variables that are replaced and at the end match it to the number in the subtable for this step. I probably wouldn't bother with this only because, every new formula will have to be tested extensively and random people won't get to change them. So you shouldn't take a run time hit to validate something only specific users can change. But, you can do something like isolate the validation code and turn it off and on so you run it for the testing phase but not for the production phase.

Looking at the BCR,
D1Text = Operation
D2Text = Individual(theoretical)
D3Text = Cumulative(theoretical)
D4Text = Individual(actual)
D5Text = cumulative(actual)

Looks like this picture was from an early example where we were trying to use Word. As I said, we went with an interim solution due to the engineers not wanting to redo all the formulas so I don't have a total working example. I was happy to find this much, the app is that old In the example, the "weightmaster" would have been calculated and then joined to the recipe table and the theoretical fields would have come from that. I'm pretty sure this was just a rough picture and the final used bound controls.

This was probably the most complicated application I ever built. But I reduced it to the point where the data drove the application so that no IT person had to get involved to add a new formula or change the instructions of an existing one.

If you are nervous about the users not testing, you can even put data fields in the tables that you populate from your code to indicate that a system test had been run for a new or changed formula. If they try to create a batch with an untested formula, "just say no"

 

[patapotato]

Thx for the clarification!

There's a lot for me to think about. Fortunately, there are no instances where multiple ingredients are used in one operation in the recipes we have. There are many without any though but that is not really a problem I think.

One thing I was wondering in regards to the variables being stored in seperate tables. I have a junction table for a m-m relationship between recipes and ingredients called 'rtblRecipeChemicalLink' atm. Now I also have a bunch of 'variables' that have to be different for every combination of recipe and ingredient (coefficient3 of water is different between RecipeA and RecipeB, both using water as an ingredient). Right now I have them stored as seperate column fields in the junction table itself. But I would rather, like you suggest, have a seperate table for those variables and then make a 1-m relationship from the junction table to said variable table. That way I can have LOTS of variables for every single ingredient/recipe combination and only where needed. Some recipes only have 1 variable for water, others 3, others 4. Most are not named and are just hard coded into an expression in the excel tables but I would rather have them properly exposed in the access database. How could I do that? I tried it out before but failed. I somehow need to link a key from the Variable table to the combination of the two keys in the junction table (RecipeId and ChemicaID). But access lists them only as seperate keys, not sure how to do this.

 

[Pat Hartman]

How could I do that?

That's a big question. Have you decided that you are going to create the Batch Control Report using an Access report or are you trying to automate word or excel? Do you have a preliminary schema?

The "step" table has a FK to the ingredient table. But the ingredient is optional so you use a left join. Not all steps take an ingredient. The step table has a FK to the "boiler plate" table. This table holds the instructions. Buried in the text are "tokens". The "tokens" will be replaced by variables in the function described below.
The variables table is a child of the step table. It includes the stepID as one FK and the variableID as the other FK AND the value for the variable in this step. The merge other variables into the text would be done with a function.

So the query that is the RecordSource of the BCR joins using a left join to the ingredient table and for the field bound to the "operation" in the document, it calls a function. The function runs the loop I described by looking up the "operation" text and looping through the variables for this step and filling the "operation" with the variable. I don't know what your text will look like but this method allows the "variable" to appear multiple times in the text. If you want it to have different values each time, then it needs to be a different variable.

It would be easier if you would post some actual examples of instructions with variables.

 

[patapotato]

I think I will be needing both a batch report, and a form in this case. But I am only busy on the form atm. Both I am aiming to do in access. There needs to be an interactive form for the operator in which they can both, view all fixed and pertinant information to the recipe they selected for the current batch, as well as enter 'input' variables like the desired total weight of the batch but also general info like, name of the supervisor, start time, end time etc. That form will then calculate the ingredient weights based on a combination of formulas. It's like a more complicated version of the Weightmaster that you have, except that it would also list the instructions seperately from the raw numbers. That was the user request. Basically, they want the whole thing to be on one form and a seperate table just for the weights and ingredients. I am prototyping the framework for this atm.

I also have two different types of 'variables'. There are 'variables' like in your case (pH, rpm, temp etc) used in the instructions themselves that look pretty much identical to the types you have in your example (right now they only exists embedded in the instruction text). An example would be :

"Heat up reactor to 78,4C"
"Add Chemical B to the mixture"
"Increase mix speed to 24 rpm"
"Control pH and note the value. Must not exceed a pH of 6"

Then there are variables used in the calculations, which is where it gets complicated:

Variables used in the calculations are things like coefficient A,B,C,D used in the calculation of the weight of ingredient A and coefficient E,F,G used in the calculation of coefficient A,B,C, which depend on coefficient H,I,J coming from the lab on that day. There are some that are constant within a recipe (all calculations in that recipe use the same value coefficient) and some that vary by ingredient used in the recipe (the coefficient value is different for every ingredient used in the recipe, and between recipes). Many of those coefficients are currently not named but hard coded into expressions in the excel files (all the ones that differ by ingredient and recipe). I'm going over the details with a chemist currently, but not all can have meaningful names. Some are just products of math with little human interpretability. However, I would like to expose those varibles properly anyway since even they sometimes needed to be changed in the past. Fortunately, the formulas that use those coefficients never really change. So the ingredient weights are calculated in a more complex structure where different coefficients change on the daily depending on the characteristics of the raw materials that need to be entered, but sometimes also just manual changes to some of those coefficients by a qualified chemist at the plant. There are often very technical reasons for it, like the pressure in the tank might be slightly different because a part got swapped out, or they need to use a different reactor that behaves differently because the one they normally use is in maintenace etc. So the form would have to list all those coefficients for the operator to inspect and make changes to if neccesary. There are default values, but they must be able to temporarily change them for the current batch. What is also important is that not all of those coefficients are used in every recipe. There are some where the whole step using coefficients EFG an HIJ does not exist and some where weights are purely calculated by ratio (like in your case). Sometimes the inbetween calculations use a different set of coefficients K,L,M, sometimes there are an additional 2 calculation steps for coefficients N,O,P and then Q,R,S. All those calculations that happen inbetween must be clearly layed out on the form the operator uses. There are obviously also new input variables for those inbetween steps. This all changes from one recipe to the next since there are many different chemicals being produced here, all with very specific requirements. That is the biggest challenge for me I think.

I've been busy simplifying the recipes to a common framework the last couple weeks but I found out that even the simplest common framework would basically require a gigantic form in which most elements are not used in most recipes. And just hiding them when not needed would result in a weird and unintuitive layout. Because the recipes are so varied in the info they need to display, as well as the calculations used and the variables that need to be manually entered, I had the idea of building a dynamic form on which control elements can be swapped out depending on the recipe. Calculations could then also be swapped out by switching between saved queries that hold them. Both, the query names and their corresponding subform names would then be saved to a table in the database that links to the correct recipe with an id informing to which subform container they belong. Then I write a module that swaps out all those subforms and queries based on the recipe when the master form gets loaded. I'm still not sure how to deal with the variables that should have default values but need to be changed on the fly and then revert back the next time they are loaded. I suppose I could write a module that is a simplified version of an audit trail tracking the change that was made to the database during the current batch in a temp table and when the form gets closed replace the modified value with the prior one. But that seems unsafe to me since the value in the database actually changes during that process. The other option would be to store only the default values in the tables, then have the queries make empty fields that correspond to them, then write a function that looks if a particular field has a default value in a table and populate the control with that value. That way I would get the default value, but the field is not linked directly to the table, so changing it would not affect them. The queries would then need to reference the control element values on the form instead of the tables for the calculations. Haven't tried that out yet, so maybe acess won't let me change the field value after placing it or there might be issues with recalculating after entereing new values when using requery or changing focus etc. Or maybe there are simpler solutions to this, I'd be happy to hear any. I have a simple version of a dynamic form right now that I am playing with that works ok. I can swap out subforms and queries on the fly, the details of which are saved in a query table linked to a recipe by id.

As for how to store the variables, some differ by ingredient and recipe combination (all the weird, non-name math related ones). I had placed them as seperate columns in a junction table between ingredients and recipes. But that basically means that I'd have to add another column to that table every time a new coefficient might be needed. It also does not help that coefficients would then share a common name between recipe/ingredient combinations. That's why I decided to better store them in a seperate "variable" or "coefficient table" similar to what you had suggested with the other type of variables and link that to the junction table. That way I'd have a different ROW for every coefficient in which I can also name it and give a description by adding those corresponding columns. I could then also have any number of coefficients for any R/I combination. Some might need 1 or 2, others 5 or 6 and they would all be able to be named independently of eachother. Aparently, what I had been looking for was a ternary relationship in this case. I'm setting that up right now trying to see if this is working better. I have also looked into class inheritance for tables but I do not think that will work as well in this context and I'm not sure if Access can even handle it. I've only seen examples using sqlserver.

It's a pretty big task, I think I'll be busy with this for a couple months at least. Which is why I will likely split up the problem and just ask more specific questions whenever they come up in seperate threads.

The instructions I can do pretty much like you described. Just have commonly repeating instruction texts saved to one table, linked to a table that holds all the instruction steps, link those to the ingredients and add another table with instruction variables as a child table. Then use a function to replace placeholders in the instruction text string with the corresponding instruction variables. I could also leave out the instruction variables and just have them permanently embedded in the instruction string. It's less elegant of course. I'd have lots of instruction strings that only differ in one little thing, so a different one every time pH might be different etc. So, I think in the long run, your solution fits better in this case. Which is why I will likely implement the instruction steps exactly like that.

 

[Pat Hartman]

But I am only busy on the form atm

You should be working on the schema. You need to understand the data you have to work with. Then we can work on presentation.

Your discussion of the calculations and coefficients is too abstract for me to offer advice other than - FIND A WAY TO PUT THE VARIABLES IN A TABLE. Occasionally, you might be able to capture the coefficients on the input form. the problem only arises if the formulas actually change. If it is just variables and the formula is a * (b +c), then who cares. If the formulas are different based on the chemical, then find a way to categorize the calculations and then use procedureA when the calc = Type4

I have also looked into class inheritance for tables but I do not think that will work as well in this context and I'm not sure if Access can even handle it. I've only seen examples using sqlserver.

I don't even know what this means. If you have lots of duplicate values but some outliers, you need to figure out how to handle the exceptions. Maybe using a child table that points to a specific chemical.

Look at the Eval() function. It might work to handle some of the variations depending on the situation.
Maybe a * (Eval(b) + c) will work and that may solve your problem. I'm pretty sure it works if b is a function. OR, you might not even need the Eval() since a *(SomeFunction + c) works fine. Then the function can do some evaluation to determine which variable to use this time.

 

[patapotato]

Fortunately, the formulas don't need to be changed by a user. And if they did, they would need to be recorded under a new recipe anyway.

However, they do change between recipes and chemicals. The formulas themselves, as well as the coefficients they use, are different for every chemical-recipe pair. So chemical A in Recipe A will have a different formula than Chemical A in Recipe B and might use coefficient A in the former and coefficient B in the latter on top of that. So I will have to relate them to that combination of recipe and chemical, rather than just the chemical. That also means that just swapping out a coefficient within the formula will not be enough to differntiate between different recipe-chemical pairs.

As for class inheritance, I meant this:

sql server - How can you represent inheritance in a database? - Stack Overflow
Entity–attribute–value model - Wikipedia

But I don't think access has the option to implement this, at least I have not seen any way in which I could. And even then, I like having coefficients in a table by themselves better for this project.

I was thinking of saving calculations in queries. Each query being used as a recordsource for a form or subform (the queries get saved like this, coefficient's default values are in tables, the queries will use those). Then switch out the recordsource from one query to the next depending on the current record used. If a subform would require a totally different content with different controls on it (which is frequent although there are types by which I can group them), I switch out the SourceObject of the subform container. The names of both the query, as well as the form it should be used in are saved to the database and related to the recipe in which they find use. They will be in two seperate tables so that one subform name can be associated with many different queries so long as the fields those queries create match those of the subform's controls. There are some subforms which need not be swapped out very often, like the one containing my version of the weightmaster, of which there are only 2 different versions neccesary. For others, like the optional calculations tables, there are about 5 different forms that I would likely need since there are 5 product types that use a very different specialty table here. In total, I think I can get away with 14 subform options and many many more queries for the whole of the 239 recipes that I have if I have counted correctly.

In the case of using different formulas for different chemicals in every recipe, assuming one subform holding controls for every chemical used in the recipe (like in a continuous form) I would use the switch() function to differentiate between chemicals within one recipe. First I wanted to use Case() in sql but I found out Access doesn't interpret that command.

The end result will be a dynamic form that changes control elements and calculations within them depending on the recipe that was selected. The layout or design of those elements or subforms, or even the masterForm does not really matter and I am not concerned with that atm. I am working out the framework by which this will function and how it will affect the design of the table relationships. Depending on how I implement this, the tables will have to look differently and some of the relationships might too.

 

[Pat Hartman]

But I don't think access has the option to implement this

SQL Server isn't implementing inheritance either. The poster was just describing a type of schema design where the common fields were stored in one table and fields associated with different entities are stored in separate 1-1 relationships. So, if you have Customers, Vendors, Employees, you can create an Entity table and store the common fields in the Entity table. Than have Three other tables, one for each type and in those tables which are 1-1 with Entity, you store the specific fields for each sub type. There is no reason you can't implement this with Jet/ACE. But it is not appropriate for your situation as you have already figured out.

We've gone beyond what I can provide as guidance in the abstract. You are going to have to provide concrete examples (several at least). You're going to have to be concrete.

You are also drifting off into la la land by thinking about making specific forms for specific calculations. You can certainly hard code everything but you will be very sorry unless you get paid by the hour and want to make this one application your life's work. Your objective should be to make an application that the users can use to create totally new recipes without you having to custom code formulas, etc.

here are about 5 different forms that I would likely need since there are 5 product types that use a very different specialty table here. In total, I think I can get away with 14 subform options and many many more queries for the whole of the 239 recipes that I have if I have counted correctly.

You haven't dug in enough to the details. A zebra is different from a horse and they are both different from donkeys. Doesn't mean you need three forms. If you look at the problem correctly, you can deal with giraffes and hypo's also.

Making shampoo, cream rinse, hair dye, hair spray, deodorant, etc are all different. Making beef stew is very different from making an angel food cake. But they are all formulas which are comprised of ingredients and instructions with different levels of difficulty and precision required. You can be very loosey goosey with beef stew and still end up with something delicious. But you can't mess with an angel food cake or a souffle. You are creating a chemical reaction and everything needs to be precise for it to work. The instructions for making a cake take a lot of different bowls. You have to separate the eggs, then beat the eggs, you have to cream the butter, you have to beat the sugar into the butter, you have to measure the dry ingredients. These are all sub steps. Then you mix them together at the end adding the ingredients in the correct order and mixing/heating/whatever as you go. Certain frostings are beaten in a double boiler because they need the heat to achieve the necessary volume and consistency. When you are working with substeps, you need a way to accumulate the weights separately. In my case, there were separate "bowls" or accumulators so ingredients got added to bowl1, bowl2, etc. Then bowl1 and bowl2 got added to the common mixer. Each little twist has to be resolved to avoid hardcoding. My favorite yellow cake is a one bowl cake which is why it is my favorite. I hate dirtying every bowl and measuring implement in the kitchen to make a difficult cake.

The end result will be a dynamic form that changes control elements and calculations within them depending on the recipe that was selected.

Bad thought process. Forms are not dynamic. If you make a form dynamic, you cannot distribute the app using the Access runtime engine or as an .accde.

and many many more queries for the whole of the 239 recipes that I have if I have counted correctly.

Are you planning on hardcoding queries? The query to build a batch should take two arguments - the formula and the batch size. If there really are different classes of formula such that you really do need different tables, then you might need couple of queries but still, each would take the two arguments.

 

[patapotato]

You are also drifting off into la la land by thinking about making specific forms for specific calculations. You can certainly hard code everything but you will be very sorry unless you get paid by the hour and want to make this one application your life's work. Your objective should be to make an application that the users can use to create totally new recipes without you having to custom code formulas, etc

Users won't be adding ANY new formulas. All the formulas we have were provided a long time ago. Users only require to change certain coefficients within those formulas from time to time on a run by run basis. I will have to enter all formulas manually myself. The last time a new recipe was added was 5 years ago and that was only a single one. The people here do not write new formulas or recipes. Recipes are not in-house. In fact, it is not only a feature that is not required by the users, they should explicitly not be able to add new recipes or formulas to the database and not be able to change formulas themselves either. That being said. I would certainly welcome some easy way for me to do it. So it's not like I hate the idea, it's just not really something the average user will ever interface with.

Bad thought process. Forms are not dynamic. If you make a form dynamic, you cannot distribute the app using the Access runtime engine or as an .accde.

Maybe I used the wrong word here or my explanation was lacking, or maybe I am just mistaken since I do not think I am an expert in this, but that should not be a problem as far as I am aware. Pls correct me if I am wrong. I am not creating new elements on a form at runtime. There is a master form, which does not change, that holds a number of subform containers. When the form gets loaded, in this case in the FormLoad() event, there is VBA code manually setting the subform containers SourceObject and then the subforms RecordSource. That has been working in in my testing but in case it is neccesary I could also set the LinkMaster and LinkChild fields manually that way. None of the controls on any of the forms are created from scratch in vba, they exist within the subforms before the program runs. As far as I am aware this should work in a runtime environment. Maybe I am wrong about this, but that's what I thought at least. In most cases online I have seen people refer to this as "dynamic" but maybe that is the wrong word. In any case, it would run from an accdb file anyway. That is how the rest of the programs are set up already, which I have no control over, and which will not change in the foreseable future. When it does, it will most likely be a move away from Access anyway.

Are you planning on hardcoding queries? The query to build a batch should take two arguments - the formula and the batch size. If there really are different classes of formula such that you really do need different tables, then you might need couple of queries but still, each would take the two arguments.

I will need multiple queries sometimes, I am aware. Not sure what you mean by "hardcoding" queries or the queries taking "Arguments"? Are you suggesting to use a parameterized query instead? How would that work with a formula? I only know of them setting criteria by user input. Or are you suggesting building a query somehow from some formula expression string in the database as well as some input values that are then substitited into the formula expression in vba? How could I run that query then using that expression? I know I can do it with the sql string but I don't think that was what you were suggesting. Are you suggesting changing the query properties of the query object itself? Or do you suggest not using queries for calculated fields at all and instead writing expresions into control elements on the form and swapping them out instead (the expressions, not the control elements)? They could be save to the DB too in some way I imagine. For someone entering a new recipe (Me) I am not sure that would be much more intuitive though. Those expressions need to reference cells from tables, from the form they are on and even fields in a parent and subform of the same or lower level since they often require variables that are user input. That user input is used as a variable in some other calculated field that MUST be present on the form and the value of that field is a variable itself in the expression used to calculate the ingredeint weitght for example. The downside I suppose is that I would need to manually set all those expressions to all the control elements that they relate to. Swapping out the whole recordsource of a subform is just one simple operation instead. Especially since in most cases many of the control elements are not needed for a particular recipe. I could hide them but there would be a lot of empty space most of the time since A LOT of control elements would not be used . The form would also need to be pretty large and I'd have to deal with resizing elements and potentially moving them around at runtime. That's also not that great of an option imo. Maybe I'll have a look at it and see what works.

The way I had thought of queries in this context was just as a means of storing formulas effecively. Assuming, for simplicity, that I have 10 recipes and there are no specialty tables etc. So it would be fine to just use one and the same subform on the MasterForm for all of them. 2 use one type of product type and thus run with formula collection A. The other 8 relate to a different product type and thus have formula collection B. In fact I only need 2 formula collections in this case (I am calling them collections since a query can contain multiple calculated fields with different formulas in them, in the sql string they will be one larger whole, but the query builder itself allows for a bit more structure). In my case, weights are not calculated by ratio for most recipes. The weight of each ingredient is calculated (for the most part) independenly from oneanother. So it is not possible to use the same expression using a variable that varies by the ingredient. I need a new expression itself for every ingredient. Sometimes they can be the same, but usually they are different from oneanother. In that sense, being able to store the expression seperately and outside of a collection like in a query would actually be better I imagine, since it would result in less wasted space. I don't need a different query just because one of the expressions in that query might be slightly different.

Formulas can differ in many ways. Technically, changing a coefficient in a mathematical expression is already a different expression. Or you could interpret it as a variable in the expression instead and then it would technically be the same mathematical expression. Depends on wether or not you expect it to ever vary and for that to be meaningful. Expressions can however, also differ in other ways. In this case they could be different in structure. Some could be mononomial, others trinomial etc. Some could be algebraic and others arithmetic. Batch size itself is a variable in the expression that is used to calculate a particular ingredient weight. There are many others. The precise chemical composition of one ingredient might be a variable that is used in said expression for one or more other ingredients used in the recipe. They might be used in the same way in identical expressions for every ingredient, they might also be used in expressions that are different for every ingredient. Obvioulsy, the same holds between recipes. Some expressions that equate to the correct ingredient weights might feature constants that are absent in others. While some expressions in my recipes reapeat in structure and only differ in the required value of a particular variable, and can thus be used for a number of different recipes and/or ingredients, A LOT of them do not. In fact, the vast majority do not.

 

[patapotato]

I know its not an easy problem to wrap your head around but I can't upload the actual recipes here. I have made another excel file which contains recipes for 4 different products. Depending on the product, the tables that are needed to display are different. Those are just 4 different examples. There can be many other combinations and I should say that even if you see the same table in two different recipes, the calculations might be different, and in many cases the actual fields do not correspond to the same things. The expressions I used are made up, but they do loosely correspond to what you might see in the actual files. So the fields that they reference might be similar, the nesting of calculated fields is similar. The actual values and variable descriptions are nonesense btw. That's also why nothing adds to the total weight. In the real recipes they do. You can take a look if you are interested but I don't expect you to come up with some solution. You helped nough as it is already. This is a layout that I came up with after going through the recipes we have and is pretty streamlined. Most recipes did not look that way in the beginning, they were all over the place. That being said there are a few important notes regarding the layout of both the excel file, as well as the layout of any form that would be present in access. There are three important sections.

The first is just a form header. That might contain a title, product info as well as some inputs like who the supervisor is, whats the date etc. That is pretty flexible. Meaning I can organize those in any way I want really and have the design used to display the info any way I want so long as it is reasonable.

The second is a section of tables and calculations. That would be what the table containers refered to in the file. With those, I have a lot less freedom in how I want to present the info. There are a bunch of governmental regulations, as well as industry and internal protocals that need to be followed here. Long story short, they have to be present on the form that the user willl engage with in a layout EXTREMELY similar to the one in the excel file. I cannot just use the calculated values and plug them into the recipe steps whenever they are relevant like in your example. They must be present in a semi tabular format at the top of the document. I cannot merge differnt tables into one, they must be seperate from oneanother, labeled and present for any partiular recipe they relate to. That was actually not the case for many of the excel files for a long time but now that the thing gets worked on it is best to actually implement it properly. So, how many of those tables are there? There is the one with the ingredients listed in the order in which they are processed, containing their calculated weights, which is one. There was a second version but after talking with the production manager aparently that version is no longer an active product. So only one table for that info. Then there are optional tables depending on the product. In my example products you can see 3 optional table containers apart from the table including the ingredient weights. That does not mean that there are only 3 different tables housing those containers, there are more. The first container on the top can either be empty, or contain any of 2 different tables here (they actually are DIFFERENT tables, one contains ratios of ingredients that went into producing the ingredient that is used now, as well as certain values calculated from those values, the other contains % values of an ingredient pair, the ratio of water to some other chemical that might have been used in the prior batch and a host of other values that that are calculated from those, both have different input variables for users). The second optional table container is the orange one. That one can contain 3 different tables depending on the recipe (I will not go over all the ways in which they differ for every version you'll just have to trust me that they are different). The third optional container can hold another 5 different tables (it could be four, that is still an ongoing discussion here). The reason that there are only 3 optional table containers is because that is the maximum number needed for any of the recipes we currently hold. Any combination neccesary for any of the products we have can be achieved with 3 optional table containers. In the future, might there be some extra special product requiring an additional one? Maybe, but from what I have been told it is unlikely, especially at the speed at which we add new recipes. Which is virtually never.

This is why I have been thinking about the design of the database with the forms in mind. There are requirements for how they have to look like and how and which information is presented for any one product, ingredient or pair thereof. I can't just willy nilly turn them into any format I want just because it is a more efficient design in line with how a database works. Having all my tables neatly normalized and then figuring out a way to present the info in one simplified, streamlined way sounds all nice and good but the requirements of the end product informs the database design itself. It is not a one way street. The tables and relationships will look drastically differnt depending on the way in which I decide to store my calculations, how I deal with having to make a form that can handle all those different tables in it's layout, how I store that info etc. The nature of the information that I need to store in my tables itself is dependent on the way in which I approach the structure of my forms.

The third section is theindividual steps in the recipe. Here I have a lot of freedom again. I can list them as steps, I can merge instructions with the values calculated above like you did, or not. It's pretty much up to me. There is also another section at the bottom but that is just a footer essentially that never really changes and just takes some input that the user puts in there. There are no calculations, no different layouts etc. It just looks like that every time. Obviously I can make the layout more intuitive maybe, but the last section is really just a bunch of textfields and labels the program does not really do anything fancy with so I do not have to worry about how that section is layed out atm.

 

[Pat Hartman]

When it does, it will most likely be a move away from Access anyway.

If you do this correctly, there won't be any reason to move away from Access. What is the point of recreating a dead application? Nothing has changed in 5 years. That's pretty dead. Go ahead. Hard code everything. I have no idea why you are even thinking of moving this app to Access.

If you can't take your Excel hat off, I can't help you. With Excel, the data and presentation layers are merged. That is NOT how Access and other relational database applications work. You persist in worrying about Excel when you need to be worrying about the schema. I have spent hours trying to help you to realign your thinking to get you into the world of relational databases and have made no impact. Good luck.