Approaches to using saved queries for doing calculations on forms

[patapotato]

Hi, new member here.

I have a bit of a particular task I need to do and would love some input. I've not been using access for very long (3 months) so I am still figuring this out as I go.

The actual application is very technical so I am going to simplify it a bit for this context.

I am building a recipe database. Currently all recipes are stored in excel files and the requirement is that all those files be removed and all data is stored in the access database itself (including all formulas used for calulation). Now, the recipes look a bit like general cooking recipes. There is a different excel file for every dish (spaghetti bolognese, lasagna, cottage pie etc.). The recipes list the general information one would expect: Every ingredient used, the weight/volume of each ingredient used, the total volume of the resulting dish, cooking instructions, safety instructions and so on. Now that in itself is easy to implement in access if you compare it to a recipe you might find online, but in this case the excel files offer additional features. Users can enter the total weight of the dish they want to make and the file calculates the corresponding amounts of ingredients that would be needed to prepare exactly that amount of said dish. So say you want to make 2kg of Lasagne, the recipe would automatically adjust ingredient values and tell you how much mince, carrots, onions and other you would need to make exactly 2kg of Lasagne. This is all easily done in excel, just write a little formula that calculates fields based on some ratio, some constants, some other fields etc.

I now have the task of making this all work in access using forms. Mind you that most recipes use different formulas to calculate ratios. Sometimes it is just a ratio based on the total end weight. Sometimes it is related to the amount of the other ingredients used. Sometimes there are additional variables that factor in like current humidity, pH, temperature at which it is being cooked, cooking time etc. So every excel file uses slightly different ways to calculate those fields based on the particulars of the recipe. The way I am currently prototyping this is by looking at a way to use queries to switch between formulas used in those excel files.

The idea was the following:
Make one form that works for most (if not all) of the recipes from the excel files instead of making a new form for every recipe.
Have one parent form that contains the product name and the corresponding recipeID.
On that parent form, put some subforms. One containing input variables and constants, another containing ingredients and calculated values.
The calculations for fields like weight or volume for those ingredients are done in the form of a query. Essentially, the queries take the ingredients that correspond to the current recipe and add calculated fields to it (weight/ volume etc) based on some formula; usually its just a ratio.
I then have the subform contain fields with corresponding names.
Now, if I want to switch to a new recipe, the formula changes, which would mean that I need to change the query. I do this by vba. I simply switch out the recordsource of the linked subform to a different query.
Since the names of all the controls of the form stay the same and just the way the corresponding fields are calculated in the query changes, this aparently works in access. This way I can change the formulas that are used to calculate the amounts for my ingredients simply by switching out the query that was useed to get the corresponding values. Essentially, I set the recordsource for the subforms manually according to which query relates to the particular recipeID in vba when the form gets opened.

In my testing, this seems to function, but I now have to decide how to save those queries and am not sure what the best approach would be. I have a few options I imagine. I could just make them, name them something, store the names of the queries used in each recipe in a table and then have a function that looks for the query needed in the recipe by recipeID on the parent form, sets the subform recordsource to that query and be done. The downside is that I have like 150 recipes. So I would need 150 queries that clutter my workspace. The other option I was debating was to store them as SQL strings in a table and then have the function look up that string, modify it if neccesary and then set the recordsource to that sql instruction. That would end up having my workspace be cluttered a lot less. The downside is that perhaps queries are bit more difficult to make changes to since they are exclusively accessed as a string in sql format. So any change would need to be made in sql. If I save them as access queries I can use the wizard to quickly make changes to it, test if it works beforehand etc. I think queries would also run faster if they are stored as access queries rather than sql strings in a table (at least I`ve read that somewhere) but none of them wuld be terribly complicated so I don't expect performance to matter much.

Curious what you all think, especially concerning any pitfalls I might encounter. If you have differing suggestions for how to tackle this application those are also welcome. The query calculation idea was the first one I had and I pretty quickly dismissed using calculated fields in my tables as an option but maybe there is some optimal way to do it.

greetings, and thx for any input.

 

[Minty]

Can I clarify the number of queries? I would imagine that you don't really need a different query for each recipe, more likely 10 or 15 different versions overall that cover all the different options.

I would store those query names in a table with a QueryID field and simply store that with the recipe.
Then use that to link the query to the form.
You could tie in with the query table other options or flags that controlled the display of the data or the calculation details for easier maintenance further down the line.

 

[arnelgp]

maybe i am wrong, but you can search here, there is already a Recipe database sample posted somewhere here.

 

[patapotato]

Can I clarify the number of queries? I would imagine that you don't really need a different query for each recipe, more likely 10 or 15 different versions overall that cover all the different options.

I would store those query names in a table with a QueryID field and simply store that with the recipe.
Then use that to link the query to the form.
You could tie in with the query table other options or flags that controlled the display of the data or the calculation details for easier maintenance further down the line.

If I expose all the constants used in the formulas then I imagine I can reduce the amount of queries I need by quite a bit. Yes, most likely about 20-30. The problem is how to store them correctly. Some are already exposed as named fields with some meaning in the excel files. But many are just numbers plugged directly into the formula expression by someone. I have no idea what they represent and no one here can tell me. The files were done 20 years ago by some guy who has since passed at some company that no longer exists and they have just been working with the files since then. Sometimes there are one or two such constants, sometimes more, sometimes none and I do not know if and which of them correspond to the same thing. I guess I could just name them constant 1,2,3 etc. No one knows what they are anyway.

If I did it like that I imagine just storing the names of the queries would be best. I will most likely also have to make a couple additional forms. Certain recipes have an extremely different makeup so I will have to make seperate forms for those and just save the form names in a similar or same table to the query names.

 

[patapotato]

maybe i am wrong, but you can search here, there is already a Recipe database sample posted somewhere here.

Do you know where exactly I should search for that? Or perhaps the title of the post? I'm new here so not sure how exactly this works. I used the search in the top right corner for 'recipe' but didn't find one. Maybe I just missed it.

 

[patapotato]

maybe i am wrong, but you can search here, there is already a Recipe database sample posted somewhere here.

Ok, I found the Stock database. I think that is the one you mean? I'll have a look at that one.

 

[Minty]

The other option would be to store the calculation methods with the recipe, but I'm not sure how that would work in practice.
You would have to get clever with the naming conventions to allow it to work with a fixed set of evaluations.

I would say that you don't need different forms for the different types of recipes, you can hide the parts that aren't relevant to the simpler ones.

 

[arnelgp]

i think it is here:
(1) Solved - Treeview | Access World Forums (access-programmers.co.uk)

 

[gemma-the-husky]

Generally for every unit, I imagine you would have to include a weight equivalent, but then you will get issues.

If your 2Kg recipe needs 6 large eggs, and a cup of milk, how many eggs and how much milk do you need for 1.5kg, or 1lb.
What about items with different densities. It's probably not so much of an issue an issue but a cup of milk and a cup of premade custard are going to be different weights, so you can't have a standard weight of a "cup". What about pancakes when the recipe just says "makes 12" and doesn't give a total weight of the prepared pancake mix. Indeed many recipes just say "serves 6 or 8", rather than specifying a weight.

Also changing weights can change cooking times. I am pretty sure that a cake of half the mixture won't take as long to cook as a large cake, but it also won't take half the time. You would also need a different sized cake tin with a smaller amount of mix. They say pastry is more like chemistry than cooking.

It sounds like it might be more difficult in practice than it ought to be.

 

[MajP]

STOP talking forms and queries! To make a good recipe database that is properly normalized is not trivial IMO. I have seen a lot of recipe databases (Use our Advanced Search at top of this form), and many are not normalized. So they are not very flexible. You need to get your tables correct, and that may take a lot of help.
If you want to be able to convert amounts, you may not want to simply multiply by a factor. You may need to convert. I database that can convert unit takes effort. Assume your base recipe calls for 1 teaspoon and you want to have a party and want to make 7 times the batch. Do you really want to show 7 teaspoons, or do you want to show 2.3 Tablespoons? You can do this in a properly designed database. This requires a conversion table, and a decision on a base unit.
Also, you may want to display in fractions instead of decimals. This can be done too. Do you really want to do some conversion and show .125 for 1/8 cup

Converting Decimals to Fractions

Here's shorter code... Function GetFraction(ByVal Number As Single, Denominator As Integer) As String Dim i As Integer Number = RoundToNearest(Number, 1 / Denominator) If Number = 1 Then Err.Raise 5, "GetFraction()", "Number must be between zero and one" Else...

www.access-programmers.co.uk

If this was me, I think a Temp Table would likely be used to store the updated recipe. This then would allow both code and queries to do the conversions, but more importantly to have a form where you can pick the update units and if necessary adjust amounts.

Bottom line lets get the all requirements and tables correct first. Can you post some of the Excel files, your proposed table structure?

 

[MajP]

With that said, Search this forum on the word recipe and download lots of examples. A lot of them do different things and have different features. Not sure you will find a perfect one that is designed to do all you want, but will give you good and bad ideas. Also there are a lot of "recipe" databases here that deal not with food, but chemicals and manufacturing. These may actually be more relevant because they tend to require conversions to package batches or convert units.

 

[MajP]

this provides an interesting feature for pulling recipes from other people recipes.
https://www.access-programmers.co.u...dividual-fields-possible.309283/#post-1666652

 

[The_Doc_Man]

I'm going to support MajP's comment: "STOP talking forms and queries!"

To start any tricky project, your first goal is to lay out a roadmap or "project bible." It is time for the "Old Programmer's Rules" which are meant to help you psychologically orient yourself on starting up a complex project.

1. If you can't do it on paper, you can't do it in Access.

This rule means that until you know what your app is supposed to do AND how it is supposed to do it AND how your results are to be presented, you are not ready to write code. Until you know the structures you intend to build, you cannot lay out records. The problem here is you will have a MAJOR "alternation of attention" issue since you have recipes for how to prepare certain dishes. You need to prepare the programming recipe of how to store, retrieve, present, and manipulate the multiple aspects of your recipes. Will you use a form to build a list of ingredients and amounts? Will you have a list of steps to follow in text or will you have some pre-defined generic instructions where you concatenate an ingredient name and an action? What do you want that interface to look like?

You have to recognize the various parts of the recipes including some things you might not have recognized. OK, you have ingredients. You have proportions for each ingredient for a "standard" (whatever that means) dish. You have ordered steps in how to combine, mix, heat or chill, lay out on the plate, etc. There are tools you need to have - the obvious spoons, knives, mixers, rolling pins, meat tenderizer hammers, microwaves, ovens, etc. You have to lay out tables to hold each of these things. You might even have a picture of what it should look like when done. The trick is to identify what will be in the database. No, the answer is NOT recipes - that is DATA. You have INSTRUCTIONS on what to do to what ingredient and when to do it and what you will use. I know you get the idea. The point is that until you have identified the entities that you must track in the database, until you know how to gather and organize and present those entities, you are not ready to write code. The corollary is that once you have your roadmap, you know what to do for each programming issue that crops up because you have already made the decision.

Nicklaus Wirth, the "father" of the PASCAL programming language, once said something like: 80% of all programming problems are derived from bad data or bad data layout. Take the time to get YOUR layout correct.

If you have a roadmap of how your parts will fit together in the database, you can start PUTTING them together. But without a good roadmap (and particularly since you admit you are in relatively new territory), how will you EVER know you have reached your destination without a good map?

2. Access never tells you anything you didn't tell it first (where "tell" may include "explain")

Access only knows how to make tables, queries, forms, reports, macros, and modules, plus relationships. Everything else is based on subject matter, and trust me - Access does NOT even know how to boil water. YOU are the subject matter expert. If you want something out of Access, you have to be sure you put that something INTO Access. If you want X, Y, and Z as outputs, it is incumbent on YOU to assure that X, Y, and Z have been input. If you want XYZ as an output, you have to assure that X, Y, and Z have been input as well as a way to mix them. Because other than in making databases, Access is dumber than a box of rocks. Sometimes to assure availability of proper inputs you might have to look at your roadmap (see above) to track back where each of those elements is presented.

3. Be sure that you never let the tail wag the dog. (I.e. don't expect the batter to turn the mixer.)

Remember, you are building a programming "image" of the process of making something. You are not programming recipes. You are programming how to retrieve and use recipes. Don't ever let the programming side of this overwhelm or alter what has to be done to keep your souffles from falling flat. If you are using this application, you build in your expertise about what you are doing as though you were doing it by hand the old-fashioned way. Remember that your Access application represents a real-world process. NEVER let the Access app dictate something to you that violates that real-world process. Since you are build an app that is basically a recipe on handling recipes, it counts as a meta-system. This is where the forest and trees start to all look alike. Build your storage and presentation system so that you can always present the correct recipe including all necessary steps. Don't allow your system to force you into a bad direction. That would be the tail wagging the dog.

This might sound formidable. It is formidable in direct proportion to the difficulty of the process being automated. You will get as much detail out of this as you put into it. I think MajP and I would agree - this IS a formidable project precisely because it deals with lots of disparate ingredients and some complex procedural steps as well. All I can end with is "good luck" and take your time in the layout.

 

[patapotato]

I might have to give some clarification here. The example of cooking recipes was just an example to illustrate the concept of the database without making it too technical, but I guess that backfired somewhat. The actual use case is chemical production. All units are in kg (batches are a minimum 30000kg, having precision below the decimal is unneccesary since the margin for error in the measurement devices is actually larger than 1kg and differences in ratio below a certain threshold have no meaningful impact on the end product as determined by a lab). There are no weight measures in any other unit than kg.

So, unit conversion is not really a problem. The whole production runs on weight measurements in kg.

Regarding the structure of the recipes:

Firstly, the layout and information on the excel tables, while similar to some cooking recipes you might see online, is somewhat different. The whole thing is designed to be read by an expert (production manager). So it only holds neccesary info on the current batch that would later be produced. There are instructions in the tables but they are largely formatted as simple comments one line at a time for each production step. The actual production is done by seperate software. The purpose of the excel tables, and later the purpose of this program, is to provide production with an easy way to calculate the required weight of all raw materials for a given batch of size X, save the info on every batch produced and on top of that, a report is printed containing all the info that was provided on the excel tables (the weight of each ingredient, total batch weight, safety and misc comments, product name, recipe name, start date, time, supervisor name etc.) The excel files look something like this:

1. A header containg product name, recipe name, current date, supervisor name, batch start time, batch end time, Batch nr, tank nr, total desired weight of the end product X, misc info as text. Also, for many of the products using a certain ingredient Z, measurements of pH, and % of ingredient Y that was used in the production of ingredient Z. Those numbers are then used as variables in many of the ingredient calculations in section 2.
2. A table containing the raw materials used in the production of product x recipe x. The table is sorted by the order in which the raw materials are added and contains columns (desired weight, measured weight *filled in after the batch ran, as well as measurements taken by the scale before and after emptying the tank *also filled in after the batch ran)
3. Each prodution step and sub steps (adding raw material 1, adding raw material 2, etc.). Each step has a number of comments in rows that inform the production worker of certain steps that they should take and certain measurements they should take and fill in right here. Like pH of the mixture at the current production step at temp x.
4. A table containing measurments taken by the lab of a sample after the batch ran. An analysis report essentially. pH of the product, temp when taken out of the tank, tank nr, silo nr, viscosity, density at 20C etc.

There are roughly 5-6 product categories. Within those categories, the excel tables are of a pretty much identical makeup. However, between those product categories, the excel files differ substantially. Some do not contain section 3 at all. Some do not contain the optional table in section 1. Some contain a tertiary table for calculations based on the optional table 1 that are then used for calculations in section 2. Some have different measurrements that need to be reported in section 4, section 3 and section 1. Some have additional constants reported in section 1.

Some of the fields should be input fields (total desired weight, start time, supervisor name, and the whole sample analysis essentially. Others should be calculated based on those inputs (weight of the raw materials used mostly, otherwise calulations in some tertiary table whose results are then used in the calculation of raw material weight. That is essentially an inbetween step that could be performed in the background but it must be present in detail on the final report and the form the production workers work with. Other fields should contain fixed values within the recipe (tank nr, ingredient comments etc.)

Right now, all fields containing fixed values are stored in the db tables directly related to a given recipe by recipe id. All calculated values are not stored in the db tables but are calculated using a query. For input variables, I am still debating how exactly to inciorporate them. Right now they are stored in the recipe table, which is probably not a good idea. As of now, there is no functionality to save the info of a current batch. That will have to be implemented eventually though.

Currently I am only working on a small prototype db with the aim of figuring out how exactly the calculations for the recipes can be stored, accessed and executed from within a form. I also have to figure out how to store all recipe data most effectively and store filled out forms in the database. There are a lot of fields in those excel tables, many of which are only used in a couple of specific recipes or product categories. I really am just experimenting at this point since I am not too familiar with what is possible within access. I agrre that it largely comes down to designing the tables and relationships correctly. The aim is to figure out how these excel files and their functionality could potentially be integrated into the current db directly without using excel at all. There are management reasons for this.

I'll try and upload a sample excel file for you guys to have a look at, although that will take some time since they are confidential and I will have to write some nonsense form with the same layout and functionality from scratch hiding all confidential info. I'll also upload a sample of the db I currently have.

 

[patapotato]

I created an application for a well known hair care products manufacturer that produced the paperwork and control file needed to produce various size batches for things like shampoo, cream rinse, and hair dye. Our formulas were usually in pounds due to the batch sizes although the dye and fragrance packs were separate formulas that used much smaller units of measures. The dye and fragrance packs were compounded separately and added as 1 item in a specific processing step. Their weight was negligible. Most formulas could be produced in several batch sizes. The formulas were actually managed in the SAP application but there was no way for SAP to manage the instructions so the Access app did that. Overnight we got a feed from the SAP application that specified what batches needed to be made the following work day. The feed from SAP also triggered some automated downloads so that our local tables did not need to hold all versions of all formulas and instructions all the time. Anyone who has ever interfaced with SAP will understand this. THEY NEVER let anyone link to THEIR tables. So, we just did nightly downloads of what we needed for the next day.

The items of a formula were presented as a percentage of the total. Invalid batch sizes were rejected if necessary. The data regarding the machines and lines was the province of the Access application and not stored in SAP. Otherwise, the instructions were merged with the components which were multiplied by the batch size to give the weight that would be entered manually on some production lines but via automation for the newest lines. So, you'd end up with a series of instructions:
82. Add 2 lbs of itemA
83. Mix at 350 RPM for 4 minutes
84. Heat to 120 F
85. Tare
86. Add 14.3 lbs of itemB
87. Mix at 300 RPM for 4 minutes
88. Increase to 400 RPM for 5 minutes
89. Stop mixer
90. Let stand 5 minutes
91. Test pH
92. Result must be >= 5.3 and <= 5.9 (the operators had instructions on how to correct if necessary)

etc.

The weights were merged with the instruction text into printed directions. The automated machines got coded files that ran the machines and automated everything with stops at certain points for the operator to test the PH as necessary.

In order to reduce the size of the batch control documents, standard procedures are managed separately and called out at the appropriate place in the instructions. Typically, there were clean out steps before and after running the batch process to ensure no contamination of the product occured.

Is this what you are looking for? The printed instructions had various places where the operator would enter notes and readings. Automating this was the next step but not part of the original project due to our time pressure.

Actually, yes that is quite similar to what we have here. In my case the instructions and the weights are divided into two seperate tables (first weights, then instructions below that). About half of the weights are calculated by multiplying with a factor based on the correct ratio for a given batch size, the other half uses more "complex" formulas (percentage of y in Z divided by amount of a in product x times constant b etc etc). There are some calculations happening inbetween, often based on characteristics of some of the ingredients that go in that come from a lab report before production and shuold be presented to the operator.

The production process is not automated yet (some are but they run on independent software and are specialty products, inventory etc is also handled seperately for them; there are some complicated legal reasons for this I am not fully privy too), so the operator has to manually enter settings at each production step. Those steps usually revolve around adding the next ingredient in the list, heating the reactor to a certain temp, mixing at a certain speed, run time etc. Inbetween those steps they often take pH measures.

What's different I suppose is that we do not use SAP, the formulas are currently stored in excel files (that used to be a neccesity because of some royalty agreement from 30 years ago that stipulated those files must be used, that agreement is now running out). As far as I am aware, there used to be an SAP application that was handling much of this in the past, but that was removed a long time ago for being too inflexible and slow to make changes to on the fly aparently. Now, almost all runs on some version of access, apart from the software that is actually managing the machinery and the production plant itself. An operator would get notified Batch A would need to be produced today at x time. They open the batch management interface (an Access program) where they fill in the info they have on the current batch about to be produced (total theoretical batch size to be produced, type of product, start time, operator name, reactor nr etc.) the program would then take that info and open an excel file that contained the specific recipe and instructions for this batch, plug the numbers the operator typed into the access program into certain fields of the excel form, which would then calculate the weights etc. The excel form then gets printed out, closed and those numbers would be passed back to the access program after calculation and stored there in a temp table waiting for the batch to be finished and the results of the lab report to come back. Then they would interface with the inventory system. The operator then writes measurements they took and other comments on the printed form at specified locations.

 

[patapotato]

Unfortunately, keeping the excel files in any format is not an option. Again, legal reasons. There is no intense time pressure, but as far as management is concerned getting rid of them is the whole reason for wanting to streamline part of the process (if it were not for the option of getting rid of those excel files they would have postponed making larger changes to the software I presume). Essentially, royalty fees are being paid for the use of those files specifically. Otherwise, importing the files would be a decent option. But the files would need to be altered heavily anyway. There are like 200 different excel files and they have been altered and created over decades. Pretty much no one cared to keep those files in a streamlined format. They are all over the place and every time a new file was created it was made up somewhat differently. Even if the contents were essentially the same. Because of the royalty agreement there could not be any changes made to those excel files to streamline them in house, which is a ridiculous stipulation imo. Every time a new file came, the guy handling the access application had to manually write some new code to get the correct values from the correct fields from those files. The excel files have a heavy layout. There are split fields, tables in long, sometimes in wide format, unnamed fields with variables in some random location, the same variables are called differently from one file to the next (even the names of the same raw materials are sometimes different) etc. Right now I am going through all the files, trying to find common ground so that I can have ONE basic format that allows for all (except special cases) recipes to be accessed within one flexible layout. Once I have that I hope to implement that new, more streamlined layout in Access itself. But I am going back and forth between access and this process to see which ways of structuring the recipe data would be easiest to handle in access.

As far as what I would like help with. I don't want anyone to solve the problem for me, I'll have to understand how the whole thing works anyway. What I had hoped for were maybe some pointers? Perhaps regarding some commonly agreed upon way to store formulas or queries for use in a similar db? Or more importantly even how they should best be stored and how the db should be constructed, or how they CAN even (what should be done depends heavily on the context I guess). I know that access has a feature for attchment fields for example, but no one really recommends that since it messes with the file size limit (and creates a lot of headaches because they are technically multivalue fields and sometimes buggy from my experimenting with them). Generally ppl just seem to store the file path instead. So, there might be a lot of pitfalls I am not aware of. I've only been working on databases for about 3 months so there is a lot of stuff I do not know.

The example databases uploaded here actually helped quite a bit in that regard, although they don't translate perfectly to this task. Maybe I'll just get to work on the basic framwework for now and then come back for more specific questions. I have a basic layout that works for most of the excel files now.

 

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

 

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

 

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

 

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