If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
many to many link question (making me dizzy)
Hey all, I think I'm on the right track but I'm missing something..
I've got 1 table called receive with a receiver number (not unique) this table links to another table called purchase with a purchase number (not unique). I used a subform so that the receiver can enter the receiver number, then the purchase number, and then view all of the parts associated with that purchase number. But, the problem now is that a separate part can have many receiver numbers (quantites can be shipped in small shipments each with an individual receiver number). Any insight on how I can link this the most efficiently? I'm sure I need a new table because it's a many to many link but I can't see exactly where. Maybe a new 1 to many table just with parts linking to receiver numbers? So with that idea how would the user input this info, should I create a subform in my subform where multiple receiver numbers can be selected? But then it seems to me it could be done more efficiently. If anyone can see what's going on here please let me know if you have an idea ! Thanks!! |
#2
|
|||
|
|||
many to many link question (making me dizzy)
comments inline.
"Alienz" wrote in message om... Hey all, I think I'm on the right track but I'm missing something.. I've got 1 table called receive with a receiver number (not unique) this table links to another table called purchase with a purchase number (not unique). i'm not sure how you're doing any linking at all, if neither the receiver number nor purchase number are unique values in their tables. do you have another field in each table that you're using as the primary key? I used a subform so that the receiver can enter the receiver number, then the purchase number, and then view all of the parts associated with that purchase number. how are you associating parts with a specific purchase number record, if the purchase number is not a unique value in its' table? But, the problem now is that a separate part can have many receiver numbers (quantites can be shipped in small shipments each with an individual receiver number). Any insight on how I can link this the most efficiently? I'm sure I need a new table because it's a many to many link but I can't see exactly where. not entirely clear on what your many-to-many relationship actually is. is it?: one receiver number can have many purchase numbers, and one purchase number can have many receiver numbers you express a many-to-many relationship between two tables via a child (linking) table that forms the many side of a one-to-many relationship *with each of the parent tables*. in this case, something like tblReceiverPurchases ReceiverNumber (foreign key from receiver numbers' table) PurchaseNumber (foreign key from purchase numbers' table) but again, if the receiver number and purchase number are not primary key in their respective tables, what is? Maybe a new 1 to many table just with parts linking to receiver numbers? So with that idea how would the user input this info, should I create a subform in my subform where multiple receiver numbers can be selected? suggest you put aside the form/subform data entry issues until you've completely worked out your table/relationship design. But then it seems to me it could be done more efficiently. If anyone can see what's going on here please let me know if you have an idea ! Thanks!! |
Thread Tools | |
Display Modes | |
|
|