Not always a 1-to-many relation (1 Viewer)

[bozrdang]

I work for a plastic injection molding company and I'm trying to create a form for setting up molds. I have tblMolds and tblParts. I then made a query from these two tables which my form is based on. I have it set up so that I select a part number from a combo box and the corresponding mold number is automatically filled in.

Our molds usually make 3-4 different parts and we usually only produce a given part from a particular mold. The problem is that we have a couple different molds that can produce the same part.

Here's an example: Part# 1 is made from Mold A; however, Mold B can also make Part#1. 95% of the time Part# 1 will be made from Mold A, but if we're in a pinch we may have to make it from Mold B.

How can I adjust my tables or relationships to allow this? Right now, if I create a new set up, I CAN change the mold number, but then it overwrite's the original mold record.

Can I do this?

 

[Jack Cowley]

I am not sure what you mean when you say you "create a new set up" so there may be other factors involved (a customers order?) that I have not taken into consideration, but here is my solution:


tblParts
PartID (Primary Key and Autonumber)
Rest of fields....

tblMolds
MoldID (Primary Key and Autonumber)
PartID (Foreign Key and Long Integer)
Rest of fields...

In the Relationship window created a join on the two tables between the PartID fields. Create a datasheet form based on the Molds table. Create a form based on the Parts table and add a subform using the Molds form. Access will link the form/subform on the PartID. Now when you select a Part you can select the which mold to use.

hth,
Jack

 

[KKilfoil]

bozrdang:

I believe you need a 'many-to-many (M:M) relationship between Molds and Parts (which involves the creation of a third 'linking' table.

Check out 'many-to-many' in this forum's search to find a number of threads to show how to do this.
Reply here if you are still stuck.