many to many link question (making me dizzy)

[Alienz]

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!!

 

[tina]

comments inline.

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