Many-to-many relationships

[Al Williams]

Pat,

I understand. Thank you very much for your help!

Al

 

[Al Williams]

John,

OK, I understand. Thank you!

Al

No, you do not understand correctly!

If you popup a form based on the second "one" side table, then you can
indeed edit and enter data using that form. If you properly set the
parameters of the NotInList event on the subform, the newly added (or
edited) record will be immediately available for use.

What you can't do is have ALL THREE tables simultaneously open for
editing: you can have either one of the "one" tables and the resolver
table on a Form and Subform, but to *edit* the other "one" side you
need to pop up a separate form. When it closes, you go back to the
form/subform.

John W. Vinson[MVP]

 

[Porous Metals Limited]

Ed..

Thank you for providing this information for another user. I found it
helpful myself. My question is do I identify the two fields I used from the
two initial tables as primary keys in the junction table as well as in the
two initial tables? I have trouble trying to figure out whether they are the
foreign keys or the primary keys....

Al
It ain't all that hard, just sounds like it.

Example

You have Lots of People
You have Lots of Vehicle Types

Each 'people' can own many vehicletypes
Each 'vehicletype' can be owned by many people.

so we have People Many --> Many VehicleTypes

To can't do M:M relationships directly, we can only do 1:M relationships

So we build an new table (People_VehicleType)
with two columns

PeopleID VehicleTypeID We make the combination PeopleID;
VehicleTypeID the Primary key

We go to relationships and add the People, VehicleTypes, and
People_VehicleType tables

Relationship
People --> People_VehicleType ( 1:M on PeopleID enforce relationship
integerity, auto update, autodelete)
Vehicletypes --> People-VehicleType (1:m on VehicleTypeID enforce
relationship integerity, auto update, autodelete)

Now when you delete a people their related records are deleted from the
table People_VehicleType
ditto for the VehicleType

Form Setup:

Forms:

frmPeople (based on the People Table), display a single form
frmVehicleTypes (based on the the VehicleType table), display a single
VehicleType
frm People_VehicleTypes (continious records)
Two ComboBox fields
1. cboPeople (bound to PeopleID) (lookup data from query based
on People with col1(id), col2 Display:[LastName] & ", " & [FirstName] & " "
& [Address], bound column: 1, Number of columns: 2, Column Widths: "0";"2")

This should give you a comboBox bound to the PeopleID but displays the
Person's last name, first name, and address in the window for lookup.


2. cboVehicleType (bound to VehicleTypeID)
(lookup data from query based on People with col1(id), col2
Display:[VehicleTypeDescription] ,bound column: 1, Number of columns: 2,
Column Widths: "0";"2")

This should give you a comboBox bound to the VehicleTypeID but displays the
Description of the vehicle in the window.

Almost there:

Now decide of you are going to enter vehicleTypes for people or People for
vehicletype or either

To handle vehicle type for people:

Open People form

Add frm People_VehicleTypes as a subform: Parent (PeopleID) child (PeopleID)

Now for a selected person you can add the types of vehicles they own

Hope this helps

Ed Warren

Access 2002: I need to read a really good discussion, with examples,
about
implementing many-to-many relationships. What issues need to be addressed
when setting up table relationships - referential integrity and cascaded
updates. How compound foreign keys are updated by Access - both
automatically and manually (if possible). What queries result in
updateable
fields and what don't. How to setup forms to easily create, edit and view
many-to-many relationships. Preferably, more than one way to do the
implementation would be discussed. I've spent a lot of time thinking and
trying to read about how to do it but haven't found enough information to
unravel my confusion. Could someone recommend a book(s) that covers this?
Thanks.

 

[BruceM]

They are primary keys (indexed, no duplicates) in what you are calling the
initial tables. If they are autonumber PKs, in the junction table they are
just Number fields. PKs are designated as such in table design view; FK
fields are established by being related to PKs.
If the fields are PKs in both the Parent and Child tables (which I'm not
sure is even possible, but in any case makes no sense) there could never be
a "many" side of a relationship. Each parent record could have only one
child record.

Ed..

Thank you for providing this information for another user. I found it
helpful myself. My question is do I identify the two fields I used from
the
two initial tables as primary keys in the junction table as well as in the
two initial tables? I have trouble trying to figure out whether they are
the
foreign keys or the primary keys....

Al
It ain't all that hard, just sounds like it.

Example

You have Lots of People
You have Lots of Vehicle Types

Each 'people' can own many vehicletypes
Each 'vehicletype' can be owned by many people.

so we have People Many --> Many VehicleTypes

To can't do M:M relationships directly, we can only do 1:M relationships

So we build an new table (People_VehicleType)
with two columns

PeopleID VehicleTypeID We make the combination PeopleID;
VehicleTypeID the Primary key

We go to relationships and add the People, VehicleTypes, and
People_VehicleType tables

Relationship
People --> People_VehicleType ( 1:M on PeopleID enforce relationship
integerity, auto update, autodelete)
Vehicletypes --> People-VehicleType (1:m on VehicleTypeID enforce
relationship integerity, auto update, autodelete)

Now when you delete a people their related records are deleted from the
table People_VehicleType
ditto for the VehicleType

Form Setup:

Forms:

frmPeople (based on the People Table), display a single form
frmVehicleTypes (based on the the VehicleType table), display a single
VehicleType
frm People_VehicleTypes (continious records)
Two ComboBox fields
1. cboPeople (bound to PeopleID) (lookup data from query
based
on People with col1(id), col2 Display:[LastName] & ", " & [FirstName] &
" "
& [Address], bound column: 1, Number of columns: 2, Column Widths:
"0";"2")

This should give you a comboBox bound to the PeopleID but displays the
Person's last name, first name, and address in the window for lookup.


2. cboVehicleType (bound to VehicleTypeID)
(lookup data from query based on People with col1(id), col2
Display:[VehicleTypeDescription] ,bound column: 1, Number of columns: 2,
Column Widths: "0";"2")

This should give you a comboBox bound to the VehicleTypeID but displays
the
Description of the vehicle in the window.

Almost there:

Now decide of you are going to enter vehicleTypes for people or People
for
vehicletype or either

To handle vehicle type for people:

Open People form

Add frm People_VehicleTypes as a subform: Parent (PeopleID) child
(PeopleID)

Now for a selected person you can add the types of vehicles they own

Hope this helps

Ed Warren

Access 2002: I need to read a really good discussion, with examples,
about
implementing many-to-many relationships. What issues need to be
addressed
when setting up table relationships - referential integrity and
cascaded
updates. How compound foreign keys are updated by Access - both
automatically and manually (if possible). What queries result in
updateable
fields and what don't. How to setup forms to easily create, edit and
view
many-to-many relationships. Preferably, more than one way to do the
implementation would be discussed. I've spent a lot of time thinking
and
trying to read about how to do it but haven't found enough information
to
unravel my confusion. Could someone recommend a book(s) that covers
this?
Thanks.

 

[Steve Schapel]

Porous,

Maybe this article will be of interest to you...
http://accesstips.datamanagementsolutions.biz/many.htm

 

[Ed Warren]

Lets get a specific example

Table1: PK1 (Primary key)
Table2: PK2 (Primary key)
TableJunction:
Fields: PK1, PK2
PrimaryKey (PK1 and PK2, unique)

In English:
Table1 has one and only one primary key (PK1)
Table2 has one and only one primary key (PK2)
The junction table has one and only one primary key (the combination of PK1
and PK2, together they are unique). You set this by going to table design
and selecting both fields and then clicking on the 'key' icon.

Ed Warren.

Ed..

Thank you for providing this information for another user. I found it
helpful myself. My question is do I identify the two fields I used from
the
two initial tables as primary keys in the junction table as well as in the
two initial tables? I have trouble trying to figure out whether they are
the
foreign keys or the primary keys....

Al
It ain't all that hard, just sounds like it.

Example

You have Lots of People
You have Lots of Vehicle Types

Each 'people' can own many vehicletypes
Each 'vehicletype' can be owned by many people.

so we have People Many --> Many VehicleTypes

To can't do M:M relationships directly, we can only do 1:M relationships

So we build an new table (People_VehicleType)
with two columns

PeopleID VehicleTypeID We make the combination PeopleID;
VehicleTypeID the Primary key

We go to relationships and add the People, VehicleTypes, and
People_VehicleType tables

Relationship
People --> People_VehicleType ( 1:M on PeopleID enforce relationship
integerity, auto update, autodelete)
Vehicletypes --> People-VehicleType (1:m on VehicleTypeID enforce
relationship integerity, auto update, autodelete)

Now when you delete a people their related records are deleted from the
table People_VehicleType
ditto for the VehicleType

Form Setup:

Forms:

frmPeople (based on the People Table), display a single form
frmVehicleTypes (based on the the VehicleType table), display a single
VehicleType
frm People_VehicleTypes (continious records)
Two ComboBox fields
1. cboPeople (bound to PeopleID) (lookup data from query
based
on People with col1(id), col2 Display:[LastName] & ", " & [FirstName] &
" "
& [Address], bound column: 1, Number of columns: 2, Column Widths:
"0";"2")

This should give you a comboBox bound to the PeopleID but displays the
Person's last name, first name, and address in the window for lookup.


2. cboVehicleType (bound to VehicleTypeID)
(lookup data from query based on People with col1(id), col2
Display:[VehicleTypeDescription] ,bound column: 1, Number of columns: 2,
Column Widths: "0";"2")

This should give you a comboBox bound to the VehicleTypeID but displays
the
Description of the vehicle in the window.

Almost there:

Now decide of you are going to enter vehicleTypes for people or People
for
vehicletype or either

To handle vehicle type for people:

Open People form

Add frm People_VehicleTypes as a subform: Parent (PeopleID) child
(PeopleID)

Now for a selected person you can add the types of vehicles they own

Hope this helps

Ed Warren

Access 2002: I need to read a really good discussion, with examples,
about
implementing many-to-many relationships. What issues need to be
addressed
when setting up table relationships - referential integrity and
cascaded
updates. How compound foreign keys are updated by Access - both
automatically and manually (if possible). What queries result in
updateable
fields and what don't. How to setup forms to easily create, edit and
view
many-to-many relationships. Preferably, more than one way to do the
implementation would be discussed. I've spent a lot of time thinking
and
trying to read about how to do it but haven't found enough information
to
unravel my confusion. Could someone recommend a book(s) that covers
this?
Thanks.

 

[BruceM]

That's another good way of doing it, but it should be noted for the benefit
of the person who posted the question that PK1 and PK2 in the junction table
still function individually as FKs. Whether or not the fields are combined
into a composite PK, they are part of the relationship:

Table1,[PK1] --> Junction Table,[PK1]
Table2,[PK2] --> Junction Table,[PK2]

With the composite key, only the combination of PK1 and PK2 is unique;
either one alone can appear many times in the junction table.

Lets get a specific example

Table1: PK1 (Primary key)
Table2: PK2 (Primary key)
TableJunction:
Fields: PK1, PK2
PrimaryKey (PK1 and PK2, unique)

In English:
Table1 has one and only one primary key (PK1)
Table2 has one and only one primary key (PK2)
The junction table has one and only one primary key (the combination of
PK1 and PK2, together they are unique). You set this by going to table
design and selecting both fields and then clicking on the 'key' icon.

Ed Warren.


"Porous Metals Limited" <[email protected]>
wrote in message

Ed..

Thank you for providing this information for another user. I found it
helpful myself. My question is do I identify the two fields I used from
the
two initial tables as primary keys in the junction table as well as in
the
two initial tables? I have trouble trying to figure out whether they are
the
foreign keys or the primary keys....

Al
It ain't all that hard, just sounds like it.

Example

You have Lots of People
You have Lots of Vehicle Types

Each 'people' can own many vehicletypes
Each 'vehicletype' can be owned by many people.

so we have People Many --> Many VehicleTypes

To can't do M:M relationships directly, we can only do 1:M relationships

So we build an new table (People_VehicleType)
with two columns

PeopleID VehicleTypeID We make the combination PeopleID;
VehicleTypeID the Primary key

We go to relationships and add the People, VehicleTypes, and
People_VehicleType tables

Relationship
People --> People_VehicleType ( 1:M on PeopleID enforce relationship
integerity, auto update, autodelete)
Vehicletypes --> People-VehicleType (1:m on VehicleTypeID enforce
relationship integerity, auto update, autodelete)

Now when you delete a people their related records are deleted from the
table People_VehicleType
ditto for the VehicleType

Form Setup:

Forms:

frmPeople (based on the People Table), display a single form
frmVehicleTypes (based on the the VehicleType table), display a single
VehicleType
frm People_VehicleTypes (continious records)
Two ComboBox fields
1. cboPeople (bound to PeopleID) (lookup data from query
based
on People with col1(id), col2 Display:[LastName] & ", " & [FirstName] &
" "
& [Address], bound column: 1, Number of columns: 2, Column Widths:
"0";"2")

This should give you a comboBox bound to the PeopleID but displays the
Person's last name, first name, and address in the window for lookup.


2. cboVehicleType (bound to VehicleTypeID)
(lookup data from query based on People with col1(id), col2
Display:[VehicleTypeDescription] ,bound column: 1, Number of columns: 2,
Column Widths: "0";"2")

This should give you a comboBox bound to the VehicleTypeID but displays
the
Description of the vehicle in the window.

Almost there:

Now decide of you are going to enter vehicleTypes for people or People
for
vehicletype or either

To handle vehicle type for people:

Open People form

Add frm People_VehicleTypes as a subform: Parent (PeopleID) child
(PeopleID)

Now for a selected person you can add the types of vehicles they own

Hope this helps

Ed Warren


Access 2002: I need to read a really good discussion, with examples,
about
implementing many-to-many relationships. What issues need to be
addressed
when setting up table relationships - referential integrity and
cascaded
updates. How compound foreign keys are updated by Access - both
automatically and manually (if possible). What queries result in
updateable
fields and what don't. How to setup forms to easily create, edit and
view
many-to-many relationships. Preferably, more than one way to do the
implementation would be discussed. I've spent a lot of time thinking
and
trying to read about how to do it but haven't found enough information
to
unravel my confusion. Could someone recommend a book(s) that covers
this?
Thanks.

 

[nyomi]

well whati need to know if your there