Form for reducing stock based on recipe (1 Viewer)

[nwest]

Hello,

I've designed a very simple stock table - it has name of chemicals and quantity (in kg - lets say I have 100kg of all). What I would like to do is design a simple form to deduct chemicals depending on chemical mix (recipe). I know I need to create a new table for the recipes.

For example,

Recipe R20 = 5kg of ChemA + 2kg of ChemB + 3kg of ChemC
Recipe R21 = 1kg of ChemA + 3kg of ChemD + 2kg of ChemE
..


So when I pick R20 recipe from the form, and say I made 20kg of this mixture (I need to enter how much of this I made), it should reduce the stock (inventory) as below:

ChemA stock by 10kg, ChemB by 4kg, and ChemC by 6kg.

What is the best way to achieve this? If you could give me some direction, or a very basic example database, that would be greatly appreciated.

Thanks!

 

[Ranman256]

tRecipes table:
Recipe , Amt, UoM, Chemical
----------------------------------------
Recipe R20 , 5,kg , ChemA
Recipe R20, 2,kg , ChemB
Recipe R20 , 3,kg, ChemC

items in Chemical could also be recipies.
use a form to give the full volume or fractions and a query would perform the math to reduce all AMTs

 

[Gasman]

Is this the same question?
https://www.accessforums.net/showthread.php?t=83730

 

[nwest]

Yes - unfortunately I have not received the help I needed from there.

@Ranman256's comment already gave me some direction, so I can start playing with the queries. I'm not sure if I can get it right yet, but that's a start!

Thanks

 

[CJ_London]

The responses on the other thread is the recommended way to manage stock.

you haven’t said how you are going to increase stock if you don’t want to go down that route

looks to me like you are approaching your requirement from an excel perspective, not a database one.

agree with ranmans suggestion for a recipe table but that wasn’t your question which was how to reduce inventory . So perhaps there is a disconnect between the question as you understand it and how it is being interpreted

 

[nwest]

OK - I'm not disagreeing that the recommended route is the best practice. But it's too complex for me. And I think you are right, normally I'm an Excel user - and Access is a bit new to me.

But here is the situation - stock will be checked manually every week, and adjusted. So I don't need very complex adding / subtracting routines. What I need, during the week, is an accurate level of stock, because while mixes are being made, stock needs to be deducted. So on Wed, I need to check if there is enough stock to make mixes on Thu. I don't think I explained this very well but I hope it makes sense.

If the only way to do is VBA and macros I won't be able to do that, just want to figure out reducing stock based on recipe.

Thanks

 

[CJ_London]

You just use an update query to amend your stock, nothing difficult about that

the problems start when something goes wrong - you update a recipe twice or with the wrong quantity for example. Or you modify a recipe for some reason

access is a database, not a larger excel. Very simply excel stores data wide and short and combines data and calculation in one, often with presentation. Databases store data in tables which are narrow and tall. Calculation is done through queries whilst presentation is done in forms and reports. They are different animals

try to apply excel principles to a database and you will end up with something that is a nightmare or impossible to maintain

good luck with your project but don’t think I can help

 

[nwest]

Thanks for your valuable feedback! DB is a different animal as you say, I'm just trying to find my way around it. Not to easiest, but good to know / learn.. Many thanks!

 

[MajP]

Lets assume as you say you enter the weekly stock values and start clean. Then you have to delete all the previous batches you made so you only delete the new batches from this weeks stock. If you do not delete them then you need to set their value to 0 or have some way to identify them so not include in the sum.
tblCurrentStock tblCurrentStock

stockID	IngredientID	StockAmount
1​	1​	100​
2​	2​	200​
3​	3​	300​
4​	4​	400​
5​	5​	500​

tblIngredient tblIngredient

IngredientID	ingredientName
1​	Chem A
2​	Chem B
3​	Chem C
4​	Chem D
5​	Chem E

tblRecipes_Ingredients tblRecipes_Ingredients

recipe_IngredientID	RecipeID	IngredientID_FK	IngredientParts
1​	R20	1​	5​
2​	R20	2​	2​
3​	R20	3​	3​
4​	R21	1​	1​
5​	R21	4​	3​
6​	R21	5​	2​

So you update tblCurrentStock. Tblingredients and tblRecipes_Ingredients are you source information.

Now you populate the batches you make. In this case 20Kg of R20 and 15 or R21

tblCreatedProducts tblCreatedProducts

productID	RecipeID_FK	TotalAmount	CreationDate
1​	R20	20​	6/1/2021​
2​	R21	15​	6/1/2021​

I will do this in pieces to make it easier to see

SELECT tblcreatedproducts.creationdate,
       tblcreatedproducts.recipeid_fk,
       tblcreatedproducts.totalamount,
       (SELECT Sum(ingredientparts)
        FROM   tblrecipes_ingredients
        WHERE  [tblrecipes_ingredients].[recipeid] =
       [tblcreatedproducts].[recipeid_fk])              AS TotalParts,
       tblrecipes_ingredients.ingredientparts,
       tblingredient.ingredientid,
       tblingredient.ingredientname,
       [totalamount] * [ingredientparts] / [totalparts] AS UsedAmount
FROM   tblcreatedproducts
       INNER JOIN (tblrecipes_ingredients
                   INNER JOIN tblingredient
                           ON tblrecipes_ingredients.ingredientid_fk =
                              tblingredient.ingredientid)
               ON tblcreatedproducts.recipeid_fk =
                  tblrecipes_ingredients.recipeid

qryRecipeAmountsUsed qryRecipeAmountsUsed

CreationDate	RecipeID_FK	TotalAmount	TotalParts	IngredientParts	IngredientID	ingredientName	UsedAmount
6/1/2021​	R20	20​	10​	5​	1​	Chem A	10​
6/1/2021​	R20	20​	10​	2​	2​	Chem B	4​
6/1/2021​	R20	20​	10​	3​	3​	Chem C	6​
6/1/2021​	R21	15​	6​	1​	1​	Chem A	2.5​
6/1/2021​	R21	15​	6​	3​	4​	Chem D	7.5​
6/1/2021​	R21	15​	6​	2​	5​	Chem E	5​

SELECT qryrecipeamountsused.ingredientid,
       qryrecipeamountsused.ingredientname,
       Sum(qryrecipeamountsused.usedamount) AS IngredientTotalUsed
FROM   qryrecipeamountsused
GROUP  BY qryrecipeamountsused.ingredientid,
          qryrecipeamountsused.ingredientname;

qryUsedIngredients qryUsedIngredients

IngredientID	ingredientName	IngredientTotalUsed
1​	Chem A	12.5​
2​	Chem B	4​
3​	Chem C	6​
4​	Chem D	7.5​
5​	Chem E	5​

SELECT qryusedingredients.ingredientname,
       [stockamount] - [ingredienttotalused] AS RemainingStock
FROM   tblcurrentstock
       INNER JOIN qryusedingredients
               ON tblcurrentstock.ingredientid =
qryusedingredients.ingredientid;

qryRemainingStock qryRemainingStock

ingredientName	RemainingStock
Chem A	87.5​
Chem B	196​
Chem C	294​
Chem D	392.5​
Chem E	495​

 

[MajP]

If you want to keep the records TblCreatedProducts then you will need some way to identify which to use in the calculation and which not. You could have a boolean field "Open". Then only include those marked true in the calculation. Then when you update the stock mark all open to false so that you start with new records only. Another possibility is to mark the stock table with a date when updated. Then mark each created product with a date. Only include those created products where the created date is less than the current stock date.

 

[arnelgp]

another sample.
almost complete app.

 

[nwest]

@MajP Thank you very much for taking the time to explain this step by step with examples - this is hugely helpful. Certainly helped me a lot to understand the logic. Thanks!

@arnelgp This is brilliant, thanks for the excellent example. Far more functional then I could ever design!