Design Help!

[JGrots]

I need help in design my database with the following criteria. I have a work order number that is unique to a specific job which has a unique part number and a tool number. (The diagram below tries to show the relationship between these entities). The part number however can be have 1 tool or 4 different tools associated with it depending on the type of volume the part has. Right now I have two tables, a work order table and a part number table which includes the tool number. This works great if there was only 1 tool assigned to each part number but that is not the case. The second image shows one of my forms where the user will type in a Work Order Number and then the part number and tool number will automatically fill in. What I want to happen is that when the user types in the Work Order number, the part number will populate but the Tool Number will have a drop down list of Tools to pick from that are assigned to that particular part number. So say for example that part number CO02003120001T can run in Tool 925, 918 and 930. The user will type in the Work Order number (925010113) and the part number (CO02003120001T) will fill in automatically. Then the Tool Number box will have a drop down list consisting of 925, 918 and 930 where the user will pick the correct Tool number that is running that part currently. Keep in mind that these Tools can also run other part numbers as well. Is there a way for me to make this happen? I hope I described the problem as clearly as possible. Thank you in advance.

[June7]

Sounds like dependent (cascading) comboboxes. Review http://datapigtechnologies.com/flash...combobox2.html

[JGrots]

That worked perfect! However, how do I design my tables to accommodate multiple tool numbers per part number. Right now I have a Work Order table that has the primary key being the Work Order Number so there are no duplicate Work Order numbers. Then I have a Products table with two columns, part number and Tool number. The part number is the primary key and one tool number is assigned to one part number since there can't be any duplicate part numbers. This, however, is causing me problems since I can't have a part number with two different Tool numbers. For example, I can't have Part #: CO02003120001T with Tool #: 925 AND Part #: CO02003120001T with Tool #: 918 in the same table. What's the best way for me to go about having multiple tool numbers for a part number?

[June7]

What is the relationship? Each part can have multiple tools. Can each tool be associated with many parts? This is a many-to-many relationship and requires another table (a 'junction' table) to relate parts and tools.

[JGrots]

Yes, a Tool can have multiple part numbers and a Part number can have multiple Tools. For example:

Part #: CO02003120001T can have 3 tool numbers associated with it: 925, 918, 930

Tool #: 925 can have 3 part numbers associated with it: CO02003120001T, VDOA234721, A1234D45

[June7]

Then need junction table and possibly form/subform arrangement for data entry.