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.

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

Very fast responding and very useful infomation.  Thank you.

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

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