Help please! How should I show children's children for a grandfather?

[Efreet]

Please forgive me for the terms I used in the title. I'm a complete noob at Access :O



I'm building a database for a manufacturing company (for free!). I have 3 different records that are in inclusive relationship: (A) transactions, (B) items, (C) materials.
(A) transactions consist of a number of (B) items, and each (B) item consists of (C) materials. (C) materials are not unique for (B) items, in other words, materials in stock can be used for different items, and (A) transaction may have two or three of the same (C) material.

I want to show what (C) materials are needed for a given (A) transaction. What kind of relationship (or whatever other methods) should I build for this database? Also, for reoccuring (C) materials in a (A) transaction, how can I combine or separate (C) materials when showing the (A) transaction?

[June7]

Will the same materials be used for each item?

Consider:

tblItems
ItemID (primary key)
other info about item

tblMaterials
MatID (primary key)
other info about materal

tblItemMat
ItemID (foreign key)
MatID (foreign key)

tblTransactions
TransID (primary key
TranDate
other info about transaction

tblTransDetails
TransID (foreign key)
ItemID (foreign key)

Might check out existing software. Google: MS Access manufacturing database. Here is one http://www.pedyn.com/access/

[Efreet]

Thank you for your reply, June7.

I have a quick question for you before I mark the thread as solved. Can you tell us why you used the foreign keys tables? From how I understand Access, they are for building many-to-many relationships. When should I used those tables?
Also, to clarify my original question, each (A)transaction is for one-time use and only have set specific (B)items, and yes the same (C)materials are used for each (B)item. To my understanding, it looked like I need to build two one-to-many relationships and somehow display this "one-to-many-to-'very-many-and-complicated'" relationship :P If the many-to-many relationships are the magic solution for this problem, I'd love to know how! Thanks again!

[June7]

Not sure I understand your confusion/question.

Need to identify entities and how they relate. Review http://forums.aspfree.com/microsoft-...es-208217.html

Each item is composed of many materials?

Each material can associate with many items?

Each transaction can have many items?

Each item can associate with many transactions?

[Efreet]

You actually understood my problem better than I did X)
Thank you for the link to the 7 page theory note. It was a very good read!