Unanswered: Relating a field in an intermediate table with multiple primary keys
I am making a database for chemical products. Each product can be made from multiple raw materials or a previously made product. To accomplish what I need, I believe I need (at least) three tables: a raw material table (Fields: autoincrement PK, raw material name), a product table (Fields: autoincrement PK, Product name), and a formula table (Fields: Foreign key from product, ingredient number from user, ingredient (related to raw material list and product list), and amount of ingredient).
My problem comes in creating the ingredient list from the raw materials and products. I can make a query to make a combined list from the two lists, but I don't know if it will auto-update every time it needs to. I also don't know if that could cause a problem by somehow losing the relationship to the original tables so, for example, if I change the name of a raw material, it will be changed when I pull up a query of formulas that contain that new name.
I realize I might now have explained this the most clearly, but I can try to clarify if need be. I am using Access 2000 and I have very limited SQL language experience.
You can store everything (end products and raw materials) in one single table (Tbl_Products in my example) and use an association table (Tbl_Composition in my example) that would act as what you name a formula table. Tbl_Products:
Product_ID: Autonumber, PK
Product_ID: Long Integer, PK(1)
Element_ID: Long Integer, PK(2)
Quantity: Long Integer
(the primary key is composed of both Product_ID and Element_ID).
To retrieve the composition of a product you can use a query such as:
SELECT Tbl_Products.Product_Name, Tbl_Products_1.Product_Name, Tbl_Composition.Quantity
FROM (Tbl_Products INNER JOIN Tbl_Composition ON Tbl_Products.Product_ID = Tbl_Composition.Product_ID) INNER JOIN Tbl_Products AS Tbl_Products_1 ON Tbl_Composition.Element_ID = Tbl_Products_1.Product_ID
WHERE (Tbl_Products.Product_ID = <Wanted Product ID>);
That way there's only one place where a product can be modified and only one place where the composition of a product can be modified.
This technique is often used in Stock Management databases.