Google Answers Logo
View Question
 
Q: Access Database Calculations ( Answered 4 out of 5 stars,   2 Comments )
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.
Answer  
Subject: Re: Access Database Calculations
Answered By: hammer-ga on 27 Dec 2004 11:31 PST
Rated:4 out of 5 stars
 
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:4 out of 5 stars
Very fast responding and very useful infomation.  Thank you.

Comments  
Subject: Re: Access Database Calculations
From: hammer-ga on 27 Dec 2004 06:24 PST
 
Joel,

What you are after is a common task often called Bill of Materials processing. 

I would recommend that you not store the result in the database. If
you do so, and someone opens up your table and changes the number of
units on a component, your stored values become wrong. In addition,
you would have to chase down and recalculate the totals for every
component that uses a particular component every time something
changes. This is known as "double-bookkeeping". The non-technical term
is "right royal pain in the a**". It's usually better to recalculate
as needed. You take a speed hit, but you get far better accuracy.

For $6.00, I can point you at an FAQ that has a sample Access bill of
materials processor that you can use as an example to modify your
design. If you actually want someone to write this for you, you may
want to reconsider your question price.
Google Answers Pricing Guidelines
http://answers.google.com/answers/pricing.html

- Hammer
Subject: Re: Access Database Calculations
From: joel_b-ga on 27 Dec 2004 10:46 PST
 
Hammer,

Thank you for the reply.  The FAQ would be a useful starting ground,
but I may get back to you afterwards with your other offer if all goes
pear shaped!

Joel

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy