How to process a assembly of parts together..

[Stephanie53]

Ok since my previous post https://www.accessforums.net/access/...elp-33469.html of Primary and Subparts seemed to have stumped alot of people lets try it this way...

I have a spreadsheet that has a break down of main part and the subparts that goes with each of them



Here is what I want to do:

I want to create a ComboBox that when select the Main part then it will autofill and/or give option to choose the subpart (might be more then one subpart pending the size of connections on the end) that belongs with the Main part.

Now here is my question:

Do I create a Table with all the Main Parts and then create a Table with all the subparts?
and if so...How can I tell it to go to the line item its needed for the Main part?

HELP!! I am starting to stress out on this delima!!

[alansidman]

You are looking to create cascading combo boxes. Here is a link to how to do it.

http://www.fontstuff.com/access/acctut10.htm

[Rawb]

I would recommend a two-table setup.

Parts Table:
PartID - The unique ID associated with a part. Used by the other Table for listing sub-parts. Autonumber, PrimaryKey
PartName - A text name for the part so a human can identify it. Text

PartAssemblies Table:
PAParentID - The PartID of the "parent" part. Long Integer
PAChildID - The PartID of the subpart(s) that go into making the parent part. Long Integer

You include every part (both parent and sub-parts) in the Parts Table. Then, for each one that has subparts, you create an entry in the PartAssemblies Table. For example, let's say you have Part A which is made by combining Part B and Part C.

Parts Table

PartID	PartName
1	Part A
2	Part B
3	Part C

PartAssemblies Table

PAParentID	PAChildID
1	2
1	3

This shows that both Part B and Part C are subparts of Part A.