Help a newbie understand simple math functions

[Butterylicious]

I am completely new to Access but probably all I will need is a gentle push in the right direction. I'd like to think I'm fairly quick at learning new things. Thank you in advance, here's where I am, (first step anyway):

Table 01 Parts List
ID - name - cost

Table 02 Item List
ID - nick name - description

Table 03 Items to parts relationship (this is where I fall apart)
ID - Item ID (relationship to table 02) - Item nickname - PartID (relationship to Table 01) - Part name - Part quantity.

What I am trying to do:
I resell items in table 02 which are built of various parts from table 01. I need to generate a table that says:
Item 1 is built from 4 of part 1, 2 of part 2, 9 of part 3, and the total cost of parts for Item 1 is x. I want to update the parts list cost on a regular basis and have the item price change reflected.

If I can understand how to make this work, I can ultimately add in labor and overhead etc etc. Baby steps for now.

Thanks again
-Richard

PS
Here's a copy of my sad little db

http://www.biggsandsmith.com/temp/Trial-db2.mdb

 

[Fuga]

I think you´ve got it almost right.

Table 3 should have 2 fields - one linking to the Item ID, and one linking to the parts ID. When you make an item from two or more of the same part, this means your table 3 will have more posts.

Table 3
ItemID PartsID
1 1
1 2
1 2
1 5

Hence item one is built from 1 of part 1, 2 of part 2 and 1 of part 5.

To calculate the cost, use a query from table 3 and table 1 (linked on partsID). Include the cost field from table 1 and ItemID from table 3. Group by the ItemID and sum the partsCost.

Fuga.

 

[Butterylicious]

eeeewwww.... so if item 1 uses 100 of part 2, then I need to enter part2 as 100 seperate records? I might need to rethink this.

 

[RuralGuy]

Here's a couple of links you may find useful, in no particular order.
BOM, with Joe Celko Nested Sets
Bill Of Materials

 

[CEH]

Have a look at this one.... Simple little DB that should have what your looking for... Open one form..."frmItem" enter your new item name,(the one made up of parts) and a sale price, this is optional... Tab to sub form ..from drop down you can pick your parts and how many are used... Should get you on the right track....
I did this quickly...... so I hope everything works Let me know if you have problems