View Question
 Question
 Subject: Access Database Calculations Category: Computers > Programming Asked by: joel_b-ga List Price: \$6.00 Posted: 26 Dec 2004 14:11 PST Expires: 25 Jan 2005 14:11 PST Question ID: 447541
 I hope someone can help me here. I am pretty new to Access DB design, especially when it omes to calculations between tables. Presently I am trying to create a series of tables and queries to produce a report highlighting the cost of a product from its component parts. First the basic tables in the following format: Table: Field Type Items_Table: Item_ID Autonumber Key Item_Name Text Ingredients_Table: Ingredient_ID Autonumber Key Ingredient_Name Text Ingredient_Cost Integer An Item_Name can have multiple Ingredient_Name values. So the overall cost of an item can be formed from a series of entries within Ingredients_Table Item_Ingredient_Table: Item_Name Text Lookup Items_Table Ingredient_Name Text Lookup Ingredients_Table Ingredient_Quantity Integer I require the ability to use the final total to make further calculations in the future so simply calculating this in a report would not quite work (I don?t think). To complicate matters, Items can also be constructed by numerous Components, so an extra field is added to the initial table: Items_Table: Item_Component Yes/No Components_Table: Component_ID Autonumber Key Component_Name Text Component_Quantity Integer Example Entry? Item1 uses Component1 * 2 and Ingredient1 * 2 and Ingredient2 * 5. Component1 uses Ingredient3 * 1 and Ingredient4 * 2 Costs: Ingredient1 = 10units Ingredient2 = 5 Ingredient3 = 1 Ingredient4 = 2 Under the above example the calculation would be: Item1=2(Component1)+2(Ingredient1)+5(Ingredient2) Or Item1=2(Ingredient3+2*Ingredient4) +2(Ingredient1)+5(Ingredient2) Or Item1=2(1+2*2)+20+25=55 After all that I want to receive a report listing each item with the total price, but with the total value stored in the database accessible to further calculations in the future: Item1 = 55 Item2 = ? Now I write this I am beginning to think this?ll never get answered. I hope someone can help me out though? Many thanks.
 Subject: Re: Access Database Calculations Answered By: hammer-ga on 27 Dec 2004 11:31 PST Rated:
 The MVPS Access Web includes a downloadable example of very basic Bill of Materials processing. It is an Access 97 database, but will convert to Access 2000 or XP without a problem. http://www.mvps.org/access/modules/mdl0027.htm The database has a form, a module and three tables. When you select an assembly from the dropdown on the form, the Output Table will delete all its records, then repopulate itself with the items for the current assembly. Select an assembly from the dropdown on Form 1, then open Output Table to see the new set of components. The code is in the module. There is also a text file that explains some of the workings. The thing to remember as you look at the code is that Bill of Materials routines are always recursive. That means that they start with the top level assembly. If any of the components in the top level assembly are assemblies, then the same routine is run to break down the internal assemblies. This continues until every component is broken down as far as it can go. This means that the code will call itself over and over again. This can be confusing if you don't understand why it is doing so. It often helps to take a known assembly and "run" the code on it, line by line, on paper. By this I mean, pretend to be the code and do what it does. If the code declares an array, you declare an array on paper. If the code fills in a slot in the array, you write the value in the correct slot on your piece of paper. This method can often help clarify a complex or recursive process by laying it out for you as is happens, step-by-step. Good luck with your Access project! - Hammer
 joel_b-ga rated this answer: Very fast responding and very useful infomation. Thank you.