Track Inventory of materials used / remaining based on recipes in access database 2013

[me.mohamed.sobhy]

Hi,


I would appreciate any help in regards to what I need to do.

I'm working on a database for my friend cafe, it has to keep a record of the following:
- Orders
- Purchase Orders
- Inventory
- Recipes Control (Add New Product/Drink means add new recipe for it)
- The cost price for each recipe determined by the quantity of materials used in each drink based on the recipe
- Cash register (Spent/Earned)

My problem is that I do not know how to track the inventory of purchased materials like (Milk, chocolate,..) and deduct from that the consumed materials based on the products/drinks sold in orders

I Figured that I need a separate tables for ( Orders/Purchase Orders/Materials Registry to hold all the info about the materials used in the recipes/Recipes tbl so I can add a new recipe or change it and create or add new drink/product in the menu/Invetory tbl to hold the quantity add to it materials purchased and deducting form it materials used based on the drinks sold)


I hope u got the basic idea please help me with how to make the inventory table hold all the quantities of the materials purchased (-) all the quantities consumed based on the recipe

Example of recipe: Milkshake: 180ml Milk + 200gm Ice Cream + 60ml chocolate syrup each component of this recipe has its own cost price I need to make it calculate the cost based on the prices of the materials and volumes purchased and the recipe itself.....please help me.

[burrina]

Inventory is basically products in, products out!
tblProducts
tblOrders
tblOrderDetails
tblCustomers

Here is a link to a site that may also help. http://www.databaseanswers.org/data_...ales/index.htm