Many to Many Relationships

C

Colin Hammond

I have got stuck creating a simple many to many relationship. I understand I
have to have an extra table with the prime keys from the tables I want to
link aqs prime keys; but how does data get into this table? What is
important in the orders products link in the Northwind database?

Colin Hammond
 
R

Roger Carlson

In a form, there are two ways. Which way you use depends on what you are
doing.

If both main tables are relatively stable (that is, data is not added or
deleted often) and only the Linking Table can be considered a transaction
table, then you can base a form on the linking table and have combo boxes
that will capture the primary keys from the main tables.

However, if one or both of the main tables are transaction tables, then the
best way is to use a Form/Subform setup. Base the main form on one of the
main tables (you'll have to choose the one that makes the most sense) and
base the subform on a query JOINING the linking table to the other main
form.

On my website (see sig below) are two small sample databases that might
help: "ImplementingM2MRelationship.mdb" and "TrainingRegistration.mdb".
The first one shows both scenerios I mentioned above. The Training
Registration application has two M:M forms; one where you can add students
and assign classes, the other where you add classes and assign students.
 
C

Colin Hammond

Thanks for your help but I have now got to the next problem. Iam
constructing a sailing club database where a boat can have many owners and a
member can have many boats. If all the owners of a boat leave the club I
want the boat to disappear. The cascade delete does not seem to work. I
could just ask the operator to use a form that lists boats without owners,
but is there an easier way.

Colin
 
D

Doug Munich

Cascade delete won't work all the way from Owner to Boat, but if you delete
all the owners of a boat and all the "join table" records are deleted with
cascade delete, then you can have a query on boats that returns only boats
that have owners, like

SELECT tblBoats.ID, tblBoats.Name
FROM tblBoats
WHERE (((tblBoats.ID) In (select BoatID from tblBoatOwners)));

Doug
 
Top