I have created a part number database. I have a form that allows you specify a part. I would like to have sub forms that allow me to select multiply manufacturer. Each Manufacturer could have several distributors. I have set up a table that contains Manufactures and Distributors. I then have query that separates the distributors from manufacturers. I did this because the data is the same for distributor and manufacturers.
On the component (my part) form, I would like a sub form that shows in datasheet view multiply manufactures. Then a sub form under manufacturer for distributors.
How do I define the relationships and set up the sub forms such that one component can have multiple manufactures and each manufacturer has multiple distributors.
Do I need to include any fields any of the tables to cross-reference.
It seems complex enough, so first you have to make a complete diagram on the paper before actually begin to build the tables, and don't mind about the forms before completing the tables and their relationships... Correct me if I am wrong but for the two first tables 'Parts' and 'Manufacturers' I think we have a many-to-many relationship here, because a part can be produced by more than one manufacturer but also a manufacturer can produce more than one part. This relationship can be obtained by using an intermediate table that will connect any part with any manufacturer. The tables must be as follows (with asterisks I have marked the primary keys):
The part can have many manufactures. Each Manufacture can have many distributors,
Manufacture 123 part numbe XXXX
Distributor abc Partnumber ZZZZ
Disctributor XYZ Part Number wwww
Manufacturer 789 partnumber 99999
Distributor abc partnumber 9xxx9xx
Distributor XYZ partnumber 88888xx8
So you see each of my partnumbers is 1 to many manufactureres
THis manufacturer has a part number that corresponds to my part number and has several distributors. Manufacturere to distributor is 1 to many
So the question you want to answer is "who do I call when I need a 1 uF Ceramic Capacitor 6.3V?"
Your part number A123456 can be manufacturer A's p/n ABC-1234 or manufacturer B's p/n 5678-DEF. The bin for this part can contain a mixture of components from different places, but they all perform a similar function.
Distributor Y carries parts from manufacturer A. Distributor X carries parts from manufacturer B. Distributor W carries parts from both manufacturers.
So you need a table for your part numbers, a table for manufacturers, and table for distributors. You've put your distributors and manufacturers in the same table and separate them with a query - it's much simpler to have two tables.
You'll also need a table to connect your part number to the manufacturer part number and a table to connect the distibutor to the manufacturer.
Nobody cares how the distributor lists the part - so you don't need to worry about their part number. If I'm a distributor for that manufacturer, I'll find that part in my inventory.