M (1 Viewer)
- Thread starter Andy Teal
- Start date
- Local time
- Today, 04:57
- Joined
- Feb 28, 2001
- Messages
- 27,320
Your first sentence places this in an "Inventory" class of problem. You consume some amount of your reagents to mix up a batch. Adding or subtracting some increment from a container total is a common, but not commonly advised, method of doing this. Most of the time, we recommend a transaction model for consumption and replenishment of supplies.
This linked article might be helpful.
Basically, you have to have some formula that says "To get 50 gallons of soap, you need x gallons of oil, y gallons of fragrance, z gallons of colorizing agent, etc. etc. (for which one would hope the total of the ingredients is 50 gallons)." Then you can plug in some computation based on the desired batch size to figure ingredient consumption. If you don't have this formula, you are stuck.
This linked article might be helpful.
Allen Browne - Inventory Control: Quantity on Hand
How do calculate the quantity-on-hand for inventory applications, rather than rely on a stored value.
allenbrowne.com
Basically, you have to have some formula that says "To get 50 gallons of soap, you need x gallons of oil, y gallons of fragrance, z gallons of colorizing agent, etc. etc. (for which one would hope the total of the ingredients is 50 gallons)." Then you can plug in some computation based on the desired batch size to figure ingredient consumption. If you don't have this formula, you are stuck.
- Local time
- Today, 04:57
- Joined
- Feb 28, 2001
- Messages
- 27,320
I'm going to give you some advice because from your question and response, it would appear that you are somewhat of a novice. The advice will not be as direct as you wish, but if I am correct, it is what you need to hear now. It has to do with projects, large and small, to be implemented using ANY software environment, but ESPECIALLY relevant to Access.
Old Programmer's Rule #1. If you can't do it on paper, you can't do it via programming.
Programming (including Access VBA and queries and such) involves the process of translating your business reality into something you can track with your programming. Stated another way, you have to intimately know your problem well enough to be able to write out the steps on paper as a guideline of how you would do this entirely by hand. Doing this will identify the data you need as well as the steps to perform in software. Because if you can't write out a detailed description of your process, you will not be able to implement it. This detailed written process becomes part of your project's documentation later. Think of this as creating a roadmap. If you don't have a roadmap, how will you know you have arrived? In more practical terms, don't start writing code if you don't know what good it will do.
Old Programmer's Rule #2. Programs can't tell you anything you didn't tell/explain to them first.
When it comes to programming, your tools are experts in data structures, and specifically for Access, that expertise is in tables, queries, forms, reports, macros, and modules. That is your software infrastructure. BUT ... YOU are the subject-matter expert. Access doesn't know doodlum-squat about soap. Remember the roadmap? It helps you to know what you want out of this process. Your roadmap has to include desired outputs, both in general format and in specific content. But that content isn't what Access "knows" - you are the subject matter expert. If you want to see X, Y, and Z as outputs, then part of your roadmap leading to those desired outputs will be the steps where they get input. If you want to see XYZ, your roadmap will ALSO include the formula or method by which X, Y, and Z are converted/computed to XYZ. It is not uncommon for you to have to start at the end of your process and work backwards to verify where & when those values are needed as inputs. (And of course you need to implement those inputs.)
Old Programmer's Rule #3. The tail never wags the dog.
When you create an app, you will implicitly implement a process for each part of your overall business process. What you want to avoid is a case where the thing you implement doesn't correspond to the actual process in question. (Exception: If you discover during construction of your roadmap that you have been doing something wrong in your actual business process, it is a separate but plausible step to correct the process.) Most of the time, the old rule from experimental science applies: If the math and the experiment disagree, it is usually the math that is wrong. The analogy for programming is that if the program does one thing and the actual business process would do something else, the program is wrong. You cannot allow the program to alter the business process. The tail cannot wag the dog.
As to helping you with the formula, that's a soft NO. I certainly understand chemical and physical mixing processes - but you have to decide what you need for your process and how you apply it. In the absence of more specifics, I can't tell you how to do that. (See rule 3 and remember, you are the subject matter expert, not me.) So it might be premature for me to do anything about your formula. If you don't know how you are going to use that formula (recipe if you prefer), it is too soon to know how it will appear.
As to the linked article... there are two main ways to do inventory.
One is to have a single variable or record that tells you how much is on hand at any given moment. You update the individual subtance's total when you consume or restock your supplies. If an error occurs with the update, your total no longer reflects reality and will perhaps negatively affect your process. This requires recognition of a consume/restock event AND the update. Presumably, for business records you would store data about each of those events anyway.
The other, which is the thrust of the Allen Browne article, is a transaction method where you have a table of transactions representing individual consumption or restock events, with a substance, a date, an amount, and the nature of the event. Then the amount of a given substance on hand is the sum of the appropriately sorted and grouped transactions. The transactions represent your consume/restock events. A query can "adjust" the amount to be a credit or debit to the supplies, and then a summation query grouped by substance gives you stock on hand. With the dates as part of the event record, you can build graphs to show consumption/production trends. More work to set up. Absolutely more likely to be correct, since here you only need to store the transactions. You run that summation query only when you need it. Which means fewer mechanical actions required on your part.
Old Programmer's Rule #1. If you can't do it on paper, you can't do it via programming.
Programming (including Access VBA and queries and such) involves the process of translating your business reality into something you can track with your programming. Stated another way, you have to intimately know your problem well enough to be able to write out the steps on paper as a guideline of how you would do this entirely by hand. Doing this will identify the data you need as well as the steps to perform in software. Because if you can't write out a detailed description of your process, you will not be able to implement it. This detailed written process becomes part of your project's documentation later. Think of this as creating a roadmap. If you don't have a roadmap, how will you know you have arrived? In more practical terms, don't start writing code if you don't know what good it will do.
Old Programmer's Rule #2. Programs can't tell you anything you didn't tell/explain to them first.
When it comes to programming, your tools are experts in data structures, and specifically for Access, that expertise is in tables, queries, forms, reports, macros, and modules. That is your software infrastructure. BUT ... YOU are the subject-matter expert. Access doesn't know doodlum-squat about soap. Remember the roadmap? It helps you to know what you want out of this process. Your roadmap has to include desired outputs, both in general format and in specific content. But that content isn't what Access "knows" - you are the subject matter expert. If you want to see X, Y, and Z as outputs, then part of your roadmap leading to those desired outputs will be the steps where they get input. If you want to see XYZ, your roadmap will ALSO include the formula or method by which X, Y, and Z are converted/computed to XYZ. It is not uncommon for you to have to start at the end of your process and work backwards to verify where & when those values are needed as inputs. (And of course you need to implement those inputs.)
Old Programmer's Rule #3. The tail never wags the dog.
When you create an app, you will implicitly implement a process for each part of your overall business process. What you want to avoid is a case where the thing you implement doesn't correspond to the actual process in question. (Exception: If you discover during construction of your roadmap that you have been doing something wrong in your actual business process, it is a separate but plausible step to correct the process.) Most of the time, the old rule from experimental science applies: If the math and the experiment disagree, it is usually the math that is wrong. The analogy for programming is that if the program does one thing and the actual business process would do something else, the program is wrong. You cannot allow the program to alter the business process. The tail cannot wag the dog.
As to helping you with the formula, that's a soft NO. I certainly understand chemical and physical mixing processes - but you have to decide what you need for your process and how you apply it. In the absence of more specifics, I can't tell you how to do that. (See rule 3 and remember, you are the subject matter expert, not me.) So it might be premature for me to do anything about your formula. If you don't know how you are going to use that formula (recipe if you prefer), it is too soon to know how it will appear.
As to the linked article... there are two main ways to do inventory.
One is to have a single variable or record that tells you how much is on hand at any given moment. You update the individual subtance's total when you consume or restock your supplies. If an error occurs with the update, your total no longer reflects reality and will perhaps negatively affect your process. This requires recognition of a consume/restock event AND the update. Presumably, for business records you would store data about each of those events anyway.
The other, which is the thrust of the Allen Browne article, is a transaction method where you have a table of transactions representing individual consumption or restock events, with a substance, a date, an amount, and the nature of the event. Then the amount of a given substance on hand is the sum of the appropriately sorted and grouped transactions. The transactions represent your consume/restock events. A query can "adjust" the amount to be a credit or debit to the supplies, and then a summation query grouped by substance gives you stock on hand. With the dates as part of the event record, you can build graphs to show consumption/production trends. More work to set up. Absolutely more likely to be correct, since here you only need to store the transactions. You run that summation query only when you need it. Which means fewer mechanical actions required on your part.
- Local time
- Today, 04:57
- Joined
- Feb 28, 2001
- Messages
- 27,320
Your simplified "fragrance in a bottle" model seems to lack information about refilling the bottle when it gets low. I can't tell you HOW or WHEN to do that because I can't see (and to be honest, don't need to see) what else you are doing. This is still a variant on an inventory problem. It isn't complete unless/until you have both consumption and replenishment accounted for. The problem with doing this with Excel is that the cells are all independent of each other UNLESS you put a formula in a cell that references another cell. That means a lot of formula repetition.
