Approaches to using saved queries for doing calculations on forms

[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.

 

[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.