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

I have a function that will convert a decimal number into a fraction, but the output is a string, not a number. The output is accurate to 1/128th of an inch. '*******************Begin Code****************** Public Function DecimalToFraction(x) Dim Temp As String Dim Fixed As Double If...

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.

 

[Pat Hartman]

Also, you need to separate the formula (ingredients list and quantities for 1 unit whatever that turns out to be) from the instructions. Beaten egg whites are not different from egg whites. The instructions separate them as well as addressing how stiff they should be when beaten. If the instructions are merged with the ingredients, this will be a much harder problem than it would be otherwise. And that may be why you think this will be so hard. Also, handling liquids separate from solids and eaches (eggs for example) means you have different calculations. Then there is the question someone brought up are you going to show 1 gallon, 4 quarts, or 16 cups? That can be done. It is just more conversions. Are you going to show 14 cups as 3.5 quarts or 3 quarts + 2 cups or 3 1/2 quarts?

Why not upload a couple of spreadsheets so we can see what you are dealing with. Make sure we know if the spreadsheets have formulas and what they do.

 

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

 

[Pat Hartman]

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.

 

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

 

[Pat Hartman]

Sounds like a very similar process. SAP stands for "sucky application program" in my opinion. It is sold to management (not the technical people and users) and always sounds good on paper. But the problem with gargantuan applications like this that are all things to all people is that they do NOTHING well and although modifications are possible in some cases, it is a painful and very slow process. SAP is like my "smart" phone. It is a phone, a camera, a flashlight, a scanner, etc. And it does none of those things well. I liked my old flip phone. It was a really good phone that didn't do anything else so it was able to excel at what it did

I'm not sure what you want from us. Confirmation you can run the whole shebang from Access? You can. Your data model could be difficult unless you can convince them to simplify. The biggest problem will be how to handle "exceptions" which in my case were dyes and fragrances because of the great difference in the size of the quantities but there is nothing to say you can't have some quantities as as pounds and some as ounces. You just need a UOM

In my case, the parent company had a bunch of subsidiaries and they bought the version of SAP that was targeted toward pharmicital production so it was OK with pills but not so good with shampoo and there were more subsidiaries that made pills than shampoo so we lost. That's why we added on lots of outside the fence processing. Although SAP managed the formulas, there was nothing magical about them and we could have done the process in Access but they'd already paid for SAP so they used that. We had to maintain shadow tables that got loaded every night with whatever we were producing the next work day as well as accept the feed that told us what we had to make.

Why not simply import the excel file into Access? There are two basic options. You can store the "recipe" in Word with bookmarks where the variables would be placed or you can store the text in tables and build reports to print the instructions. One of the reasons we went with the former is because they were already managing word documents and using macros to fill them so it was fairly easy to stick with the bookmarks. I suppose you could do the same type of things with Excel but it will be trickier to automate. The reason we didn't implement the reporting data entry as the batch was being produced because all the data was in the word document and so we couldn't easily produce something usable with a form. So, they continued their existing process of logging stuff on the printed document. There's a lot of data entry and re-validating the formulas when you have to go from Word or Excel to Access but once there, you end up with a lot more flexibility in the end. Depending on how standard your spreadsheets are, you might be able to automate a lot of the process. Most of the instruction text is essentially boiler plate with a couple of variables. To simplify, you can limit each statement to one variable or a low and high range. Variable types might be materialID, temperature, pH, RPM, etc.

We also printed a version of the instructions that was just the ingredients and which we called the weightmaster. It was just a list of all the ingredients and their amounts with a running total of weight so the operator always had a point of reference when he was working manually. We validated the calculations with that report/form but we also printed the expected running totals on the instructions for each batch size.

From the outset, Access produced the data files required to drive the automation for the lines that had automation. That was actually pretty easy. We just created a .csv. But, even if the automation requires something more complicated, you can still do it from access. I've created EDI (Electronic Data Interchange) files from and those are probably as bad as it gets. Reading them is even harder than writing them but Access can do it

 

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

 

[Pat Hartman]

There is no standard for storing formulas. We can't even offer suggestions without seeing your problem. 200 seems like a lot but in reality, it is not. You need to examine enough of the workbooks to understand what the common phrases are so you can make an Access table that can hold the instructions. Then you need a table with ingredients. Then you need a table for formulas. For the formula line items, they pick the text from the common phrases table and the ingredients from the ingredients table. You may also need a self referencing formula table so that standard instructions like clean out can be written as a formula and then added with a self reference to the formula table.

One knowledgeable person will be able to enter the 200 formulas manually in a week or less. You just need to create a logical framework. I wouldn't even bother trying to do an automated conversion if the workbooks ae non-standard.

1. Stay away from multi-value fields
2. Stay away from table level lookups (use combos on forms)
3. Doesn't sound like you even need attachments. But if you were making a cookbook, they would be used to hold pictures of the steps if you wanted to hold them in the database. The better solution is to link to them to avoid bloat or to create a separate table to hold them. The attachments have a cool control but behind the scenes, they are stored in a separate table just as you would do if you were to do it manually. However, 1, 2, and 3 CANNOT be converted to SQL Server so you wouldn't be able to easily upsize the app. You would need to modify all those items and do them the way you should have from the beginning The only benefit to these three data types is the cool control. They are all stored in a properly normalized way but the tables are hidden so you can't alter them. If you do it yourself, you don't get the cool control but you have more control in the long run and an up sizeable application.

Use a sound naming convention using only letters and numbers and the underscore. There are two common styles. CamelCase where each word is Capitalized and the_underscore where the underscore is used as the separator. I prefer CamelCase. I type camelcase and Access "fixes" it for me when I move on. This is a sanity check and helps to avoid typos. I find the underscore jarring to look at and annoying to type because I need to shift to type it so I only use them for emphasis and that is rare.

Get a list of the VBA functions by category NOT alpha. To use the alpha list, you need to KNOW the name of the function you are looking for. With the category list, you can search the section on "date" functions and zero in on what you need far more easily. Print it and keep it by your side to help you with coding or to help you to avoid coding Spend some time looking at control and form events. The MOST IMPORTANT event of all is the Form's BeforeUpdate event. It is the event where you can do your validation and stop Access from saving a bad or empty record. Here's a link to a video. It is too long but I had a lot to say and show on the topic.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

I'll attach a sample recipe database to give you a start. It uses poor names for objects and I think it is an old MS Access sample. It puts all the instructions in a large text field but that isn't what you want. You want line by line instructions and the material on that line if it is an "add" operation.

And some links to some other useful samples.

Useful code AND the effects of using bad column names

This database has several useful code procedures that you can copy into your BE to fix up defaults you don't like: 1. Change the allow ZLS string property to No If you want to make a text field required, it makes no sense at all to have the Allow ZLS property set to Yes. 2. Turn off sub...

www.access-programmers.co.uk

Sample Date Functions

This database contains a number of date functions that you can simply copy into your application. There is a tabbed form to show you how to use the calculations. A partial list of functions is: Business days between two dates. Business day + days = next business date Easter (Western) Age...

www.access-programmers.co.uk

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

Autofill and other examples

This database shows examples of how autofill works. It also shows examples of filtering with a multi-select listbox. I added an updated version of the database with additional examples.

www.access-programmers.co.uk

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk

Replacement Switchboard

This Switchboard form is based on the last VBA version which I think was A2k. I expanded it to allow for 12 items rather than 8 and you can easily change it to do more. I've included directions. This updated version has two switchboards. One more traditional and the other is big buttons...

www.access-programmers.co.uk

Switchboard with simple security

This is a custom switchboard. It expands on the Switchboard Items table to add four additional columns to define view/edit/add/delete security levels for switchboard items and forms/reports referenced by them. It includes tables to manage users and also to assign them security levels. The user...

www.access-programmers.co.uk

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk

I also found this recently. You are probably beyond it but it is only 5 minutes and gives you a good feel how objects work together to make an application.
The four Access objects

Access form add ole object youtube - Search Videos

www.bing.com

Once you create your initial schema, please post it and we'll look at it. Don't even think about other objects except possibly the final output because after all, you do need to account for everything that the user needs to see and enter.

Sometimes for convenience, we create the initial application with the tables embedded. But once you think the tables are close to final, you should split into the FE/BE and continue development that way. That allows you to more easily allow the user to test one version while you are moving on to the next. If you need help with the distribution of the FE, there's lots here or post back.