Many-to-many relationships

[Al Williams]

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.

 

[Ed Warren]

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

 

[Pat Hartman]

Here's a link to a sample db I posted on another site.
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=40821&highlight=manytomany

 

[Al Williams]

Pat,

This is great! Just what I needed, a clear design that is as simple as
possible, that works, and that I can “poke†to see how it was set up and what
happens when I enter data.

As you might imagine, I have some questions:
1. When you built the subforms, you used a query for each subform that
picked up the ID field from the junction table that makes it possible to use
Link Master Field and Link Child Field between the junction table and the
form’s ID. But when you built the query for the combo boxes, you included
only fields from tblVenue or from tblCustomer. I don’t understand why.
2. Some of the fields in the subforms are not updateable. The fields that
are common to the junction table are but the others aren’t. Would you please
explain why they aren’t?
3. In tblBookings, BookingDt is part of the compound foreign key. Why did
you include it?
4. Can I use synchronize techniques, such as I have read about which use
“On Current,†to keep the forms in synch if both are open?

I’ll probably realize that there are other questions I should ask but the
rest seems clear. I should say that I like graying out the fields that are
not updateable, neat idea.

Thanks very much! I’ve spent dozens of hours trying to figure out how to do
this.

Al Williams

 

[Al Williams]

Ed,

Thank you! I followed most of that but I don’t understand how the queries
are done to setup the combo boxes. My confusion has been that I don’t
understand how the compound foreign keys in the junction table are updated
when a record is changed. Could you explain? Thanks.

Al Williams

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.

 

[John Vinson]

Ed,

Thank you! I followed most of that but I don’t understand how the queries
are done to setup the combo boxes. My confusion has been that I don’t
understand how the compound foreign keys in the junction table are updated
when a record is changed. Could you explain? Thanks.

The Subform's Master Link Field and Child Link Field take care of the
synchronization. When you move to a new mainform record, the subform
is requeried to display the data for the link field.

A Combo box *gets* data from one table or query - its RowSource - and
*puts* data into another table, the field specified by its Control
Source. The field that gets stored may or may not be visible in the
combo box; it typically isn't - you'ld have a numeric ID as the bound
column, with zero width in the ColumnWidths property of the combo.

John W. Vinson[MVP]

 

[Ed Warren]

I'm not sure I know what you mean by 'when the related record is changed'
The only change that could affect the 'junction table' is a change in the
key field ( a short route to madiness), but even in this case Access will
take care of the change if you have the relationship set up to automatically
update. You just don't have to worry about it.

When you add a new record to the Parent table a new Key Field Id is
generated and used in the linked subform to fill in the appropriate field.

In the example I gave if you are working from the People Form, when you add
a new "vehicletype" in the subform, access adds the PeopleID from the parent
form to the junction table and you select the appropriate VehicleType from
the ComboBox and your done.

More on the example.

Ok you have a "People" table
PeopleID (Primary Key)
LastName (text)
FirstName(text)
Address (text)
City(text)

989|Williams|Al|Oak St|SomePlace

What you want in your lookup is:
989(hidden)|Williams, Al Oak St, Someplace (this is what you see)

Query:

lookupPeople

Select PeopleID, [LastName] & ", " & [FirstName] & ", " &[Address] & ", " &
[City] as Display
From tablePeople
Order by [LastName],[FirstName],[Address],[City];

Make lookupPeople the row source for the comboBox "txtPeople" bound to
column 1 with 2 columns and columnwidths "0";"2"

With regard to the updates, Access handles these for you if you have a
subForm properly linked into the MainForm.
When you add a new row to the junction table's related form -- Access adds
the current Related Parent form key field to the table.
The autoupdate, delete parts of the relationship handles the case you
'update' the key field (almost never) and when you delete the key record all
the 'child records' are deleted.

My recommendation: Put together a simple example (like above) and play
around with it by adding, deleting, and changing some records. All will
become clear.

Ed Warren.

Ed,

Thank you! I followed most of that but I don't understand how the queries
are done to setup the combo boxes. My confusion has been that I don't
understand how the compound foreign keys in the junction table are updated
when a record is changed. Could you explain? Thanks.

Al Williams

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.

 

[Al Williams]

John,

I tried to setup a form based on the table on one side of the M:M
relationship and a subform based on the table on the other side. When I
tried to setup the Link Master Field and Link Child Field I encountered a
problem -- no matching fields. I then tried to make the junction table a
subform that was inserted between the form and the first subform. But, I
couldn't get the Link Master Field and Link Child Field links setup to work.
Thanks.

Al Williams

 

[John Vinson]

John,

I tried to setup a form based on the table on one side of the M:M
relationship and a subform based on the table on the other side.

That will not work because there is no direct link between those
tables.

When I
tried to setup the Link Master Field and Link Child Field I encountered a
problem -- no matching fields.
Exactly.

I then tried to make the junction table a
subform that was inserted between the form and the first subform. But, I
couldn't get the Link Master Field and Link Child Field links setup to work.
Thanks.

You need to decide which "one" side you want to see routinely, and
make that the recordsource for the main form. You would then make the
junction table the recordsource for the subform. The other "one" side
WOULD NOT BE SHOWN; you might have a combo box on the subform
displaying a human-meaningful identifier to select which "other one
side" record you want to insert.

You can have a *separate* mainform for the other "one" side, and pop
it up as needed in the NotInList event of the combo box on the
subform; you can *display* (but not insert) values from the "other one
side" on the subform. But you can't readily have all three tables
visible simultaneously.


John W. Vinson[MVP]

 

[BruceM]

You would run into problems with that combined primary key if one person owns
two of a particular type of vehicle. Given that some people are very
dedicated to a particular type of vehicle it is certainly possible. The
combined PK is not guaranteed to be unique, and therefore is a questionable
choice as a PK.

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.

 

[Ed Warren]

Sometimes one must keep things simple. (Required in this case to try to
explain M:M relationships.

I come from a bit not stored is a bit saved background.

Not saving an additional field saves a few bits or couple of Bytes and over
a large database them little bits/bytes can add up in search time and file
size. They can get important in designs storing a several of terabytes of
data with a few 100 millon records.

Yes I could add a unique Key field, and that would be appropriate if what I
wanted was to know how many jeeps one owned, but then I may want to add a
field to track the year of each, and/or I may want to put the number of
jeeps in another field e.g.
or maybe my VehicleType includes the model/year or ....... or...... or.....

PeopleID |VehicleTypeID| Year|numberowned
or
PeopleID |VehicleTypeID| numberowned
or
UniqueKey |PeopleID| VehicleTypeID
or
.......

Using a 'Unique Primary Key' is appropriate when it is required and
inappropriate when not.

I'm no expert with regard to the theory of database normalization, but best
I can recall you must remove any 'repeating' rows, to get to third normal
form.
Your design would produce 'repeating rows', and while convienient and simple
this deviates from the strick standard. When required, I certianly do this
rather than build yet another table to tie stuff together).

E.g.
Key PeopleID VehicleID
1 1 2
2 1 2
3 1 2
4 1 3

Note rows 1,2,3 are repeating rows.


See all this is already too much for my feeble mind to grasp, gotta go have
another cuppa coffee to wake up! ;>


Ed Warren.

You would run into problems with that combined primary key if one person
owns
two of a particular type of vehicle. Given that some people are very
dedicated to a particular type of vehicle it is certainly possible. The
combined PK is not guaranteed to be unique, and therefore is a
questionable
choice as a PK.

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.

 

[Pat Hartman]

1."But when you built the query for the combo boxes, you included
only fields from tblVenue or from tblCustomer. I don't understand why."
The RowSource for a combo is the table(or a query of that table) that
defines the universe of values for the combo. The RecordSource for the
subform is the table(or a query of that table) where the data will be
stored.
2. All the fields in the subform are technically updatable. They are not
updatable on the subforms in the example because I locked the fields to
prevent accidental updates. When you show "one-side" data in a "many-side"
form, it is best to disallow updates. If a row in the subform were to
change data in the "one-side" table, the value would be changed for ALL
"many-side" records so rather than explain how this works, I just lock the
fields. If you want to see it work, open the query directly. Notice what
happens when you change something in the one-side table. If you decide to
allow your users to update the one-side from the many-side, you can change
the locked properties to No.
3. Sometimes a many-to-many relationship can occur more than once for the
same pair of primary keys. This is an example of that situation. The same
customer can book the same venue multiple times. Without the date field as
part of the primary key, the customer would be limited to booking a given
venue only once. A similar situation occurs with students and classes.
Normally a student will only take each class once but occasionally a student
will need to repeat a class so the Semester or some other date field is
included in the primary key for this relationship also. In the case of
Student/Class, the relation table would also store the final grade for the
class.
4. Sure but I would put code in the form to make sure the form you want to
sync is open so you don't have an error if it isn't. I don't think that my
example is a good candidate for this since each main form contains a
subform. I built the forms that way to show you that the relation table can
be maintained from either perspective. Usually, an application will lean
toward using one side of the relationship as the driver and all/most entries
to the relationship will be made from that perspective. In that case,
having the other entity visible on a separate main form would be
appropriate. So, for example, if you always made the bookings from the
customer form, you could have the venue form always open to the venue that
was "current" in the customer main form.

 

[BruceM]

An Access primary key is unique. That is what makes it a primary key.
Regarding your point about an extra field adding considerably to the size of
a large database project, for purposes of this forum I tend to assume that
people are working on relatively small projects. Somebody developing a
database to store terabytes of information or hundreds of millions of records
is probably past the point of needing to ask about many-to-many relationships.
I have noticed there is a long-running debate about a separate PK field
rather than a "natural" PK based on data in the record. All I will say on
that topic is that either approach is probably valid. I find that
autonumbers (or such things as Employee IDs or invoice numbers) work well,
have the advantage of not leaving me to wonder whether I have satisfied the
"uniqueness" requirement, and are simple to implement.
Presumably each record in the junction table would contain additional
information such as year, model, color, etc., they are not really repeating
records. A family with two children could well contain repeating information
in some fields of a Children table, but that doesn't mean they are repeating
records.
One other point has to do with cascading deletes. If you remove a person
from the database (I would be inclined to use a query to filter them out of
the recordset rather than deleting their records) then it makes sense to also
eliminate related records from the junction table. However, if you delete a
record from the vehicle table and have cascade delete set up you would be
eliminating from the database that anybody had ever owned that type of
vehicle. That may not be what you intended.
I should probably study more about database theory so that I can jump into
discussions about first normal form and all that, but for now I am at the
point of asking "What if somebody owns two Fords?". Please don't get me
wrong. I think it's great that you have put so much time and thought into
your responses. That I can navigate databases now is due in large part to
people like yourself who were generous with their time and knowledge when I
was getting started. I am just suggesting alternative thoughts on some
particular points. It is meant as a discussion, not a criticism or anything
negative. I sincerely hope the spirit of my remarks is coming across as
intended.

Sometimes one must keep things simple. (Required in this case to try to
explain M:M relationships.

I come from a bit not stored is a bit saved background.

Not saving an additional field saves a few bits or couple of Bytes and over
a large database them little bits/bytes can add up in search time and file
size. They can get important in designs storing a several of terabytes of
data with a few 100 millon records.

Yes I could add a unique Key field, and that would be appropriate if what I
wanted was to know how many jeeps one owned, but then I may want to add a
field to track the year of each, and/or I may want to put the number of
jeeps in another field e.g.
or maybe my VehicleType includes the model/year or ....... or...... or.....

PeopleID |VehicleTypeID| Year|numberowned
or
PeopleID |VehicleTypeID| numberowned
or
UniqueKey |PeopleID| VehicleTypeID
or
.......

Using a 'Unique Primary Key' is appropriate when it is required and
inappropriate when not.

I'm no expert with regard to the theory of database normalization, but best
I can recall you must remove any 'repeating' rows, to get to third normal
form.
Your design would produce 'repeating rows', and while convienient and simple
this deviates from the strick standard. When required, I certianly do this
rather than build yet another table to tie stuff together).

E.g.
Key PeopleID VehicleID
1 1 2
2 1 2
3 1 2
4 1 3

Note rows 1,2,3 are repeating rows.


See all this is already too much for my feeble mind to grasp, gotta go have
another cuppa coffee to wake up! ;>


Ed Warren.

You would run into problems with that combined primary key if one person
owns
two of a particular type of vehicle. Given that some people are very
dedicated to a particular type of vehicle it is certainly possible. The
combined PK is not guaranteed to be unique, and therefore is a
questionable
choice as a PK.

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.

 

[Ed Warren]

I yield (this is becoming a number of angels dancing on a pin-head
discussion).

Please help Al, as he proceeds to grope with the M:M relationships.

Ed Warren.

An Access primary key is unique. That is what makes it a primary key.
Regarding your point about an extra field adding considerably to the size
of
a large database project, for purposes of this forum I tend to assume that
people are working on relatively small projects. Somebody developing a
database to store terabytes of information or hundreds of millions of
records
is probably past the point of needing to ask about many-to-many
relationships.
I have noticed there is a long-running debate about a separate PK field
rather than a "natural" PK based on data in the record. All I will say on
that topic is that either approach is probably valid. I find that
autonumbers (or such things as Employee IDs or invoice numbers) work well,
have the advantage of not leaving me to wonder whether I have satisfied
the
"uniqueness" requirement, and are simple to implement.
Presumably each record in the junction table would contain additional
information such as year, model, color, etc., they are not really
repeating
records. A family with two children could well contain repeating
information
in some fields of a Children table, but that doesn't mean they are
repeating
records.
One other point has to do with cascading deletes. If you remove a person
from the database (I would be inclined to use a query to filter them out
of
the recordset rather than deleting their records) then it makes sense to
also
eliminate related records from the junction table. However, if you delete
a
record from the vehicle table and have cascade delete set up you would be
eliminating from the database that anybody had ever owned that type of
vehicle. That may not be what you intended.
I should probably study more about database theory so that I can jump into
discussions about first normal form and all that, but for now I am at the
point of asking "What if somebody owns two Fords?". Please don't get me
wrong. I think it's great that you have put so much time and thought into
your responses. That I can navigate databases now is due in large part to
people like yourself who were generous with their time and knowledge when
I
was getting started. I am just suggesting alternative thoughts on some
particular points. It is meant as a discussion, not a criticism or
anything
negative. I sincerely hope the spirit of my remarks is coming across as
intended.

Sometimes one must keep things simple. (Required in this case to try to
explain M:M relationships.

I come from a bit not stored is a bit saved background.

Not saving an additional field saves a few bits or couple of Bytes and
over
a large database them little bits/bytes can add up in search time and
file
size. They can get important in designs storing a several of terabytes of
data with a few 100 millon records.

Yes I could add a unique Key field, and that would be appropriate if what
I
wanted was to know how many jeeps one owned, but then I may want to add a
field to track the year of each, and/or I may want to put the number of
jeeps in another field e.g.
or maybe my VehicleType includes the model/year or ....... or......
or.....

PeopleID |VehicleTypeID| Year|numberowned
or
PeopleID |VehicleTypeID| numberowned
or
UniqueKey |PeopleID| VehicleTypeID
or
.......

Using a 'Unique Primary Key' is appropriate when it is required and
inappropriate when not.

I'm no expert with regard to the theory of database normalization, but
best
I can recall you must remove any 'repeating' rows, to get to third normal
form.
Your design would produce 'repeating rows', and while convienient and
simple
this deviates from the strick standard. When required, I certianly do
this
rather than build yet another table to tie stuff together).

E.g.
Key PeopleID VehicleID
1 1 2
2 1 2
3 1 2
4 1 3

Note rows 1,2,3 are repeating rows.


See all this is already too much for my feeble mind to grasp, gotta go
have
another cuppa coffee to wake up! ;>


Ed Warren.

You would run into problems with that combined primary key if one
person
owns
two of a particular type of vehicle. Given that some people are very
dedicated to a particular type of vehicle it is certainly possible.
The
combined PK is not guaranteed to be unique, and therefore is a
questionable
choice as a PK.

:

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.

 

[Al Williams]

Pat,

Yesterday, I participated in the federal TOP OFF 3 exercise (at a very low
level) so didn’t have time to look at your response. My comments and two
questions concerning your response:
1. OK, I now understand. Your answer addressed the main point of my
confusion. Thank you.
2. I had to play with it by enabling updates in the form but I now
understand. Thank you.
3. Why would the final grade be included in the relation table? I believe
I understand why the date field is included. Does the grade field go with
the date field?
4. I asked the question to make certain that I understand – and I now do –
and because I think I would want both forms open since I’m inclined to
provide not only the information the user needs but also supplemental
information that may be helpful at times. Thanks.

Thank you very much, Pat. This has been a big help!

Al

 

[Al Williams]

John,

Do I understand correctly that if I "popup" the other "one" side when adding
an item to the combo box values (because it is not currently in the list)
that I can only display values on the other "one" side but can't insert or
edit values? Is that because the forms aren't synchronized?

Al

 

[Al Williams]

Bruce,

Thanks. I especially appreciate the comment about filtering out no longer
needed recordsets than than deleting them. I hadn't considered that. Thank
you.

Al

An Access primary key is unique. That is what makes it a primary key.
Regarding your point about an extra field adding considerably to the size of
a large database project, for purposes of this forum I tend to assume that
people are working on relatively small projects. Somebody developing a
database to store terabytes of information or hundreds of millions of records
is probably past the point of needing to ask about many-to-many relationships.
I have noticed there is a long-running debate about a separate PK field
rather than a "natural" PK based on data in the record. All I will say on
that topic is that either approach is probably valid. I find that
autonumbers (or such things as Employee IDs or invoice numbers) work well,
have the advantage of not leaving me to wonder whether I have satisfied the
"uniqueness" requirement, and are simple to implement.
Presumably each record in the junction table would contain additional
information such as year, model, color, etc., they are not really repeating
records. A family with two children could well contain repeating information
in some fields of a Children table, but that doesn't mean they are repeating
records.
One other point has to do with cascading deletes. If you remove a person
from the database (I would be inclined to use a query to filter them out of
the recordset rather than deleting their records) then it makes sense to also
eliminate related records from the junction table. However, if you delete a
record from the vehicle table and have cascade delete set up you would be
eliminating from the database that anybody had ever owned that type of
vehicle. That may not be what you intended.
I should probably study more about database theory so that I can jump into
discussions about first normal form and all that, but for now I am at the
point of asking "What if somebody owns two Fords?". Please don't get me
wrong. I think it's great that you have put so much time and thought into
your responses. That I can navigate databases now is due in large part to
people like yourself who were generous with their time and knowledge when I
was getting started. I am just suggesting alternative thoughts on some
particular points. It is meant as a discussion, not a criticism or anything
negative. I sincerely hope the spirit of my remarks is coming across as
intended.

Sometimes one must keep things simple. (Required in this case to try to
explain M:M relationships.

I come from a bit not stored is a bit saved background.

Not saving an additional field saves a few bits or couple of Bytes and over
a large database them little bits/bytes can add up in search time and file
size. They can get important in designs storing a several of terabytes of
data with a few 100 millon records.

Yes I could add a unique Key field, and that would be appropriate if what I
wanted was to know how many jeeps one owned, but then I may want to add a
field to track the year of each, and/or I may want to put the number of
jeeps in another field e.g.
or maybe my VehicleType includes the model/year or ....... or...... or.....

PeopleID |VehicleTypeID| Year|numberowned
or
PeopleID |VehicleTypeID| numberowned
or
UniqueKey |PeopleID| VehicleTypeID
or
.......

Using a 'Unique Primary Key' is appropriate when it is required and
inappropriate when not.

I'm no expert with regard to the theory of database normalization, but best
I can recall you must remove any 'repeating' rows, to get to third normal
form.
Your design would produce 'repeating rows', and while convienient and simple
this deviates from the strick standard. When required, I certianly do this
rather than build yet another table to tie stuff together).

E.g.
Key PeopleID VehicleID
1 1 2
2 1 2
3 1 2
4 1 3

Note rows 1,2,3 are repeating rows.


See all this is already too much for my feeble mind to grasp, gotta go have
another cuppa coffee to wake up! ;>


Ed Warren.

You would run into problems with that combined primary key if one person
owns
two of a particular type of vehicle. Given that some people are very
dedicated to a particular type of vehicle it is certainly possible. The
combined PK is not guaranteed to be unique, and therefore is a
questionable
choice as a PK.

:

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.

 

[Al Williams]

Ed,

Thank you very much for your responses. One of the problems I had was not
knowing how to implement your suggestions. That's because most of my
confusion was at the "how to" level. Between you, Bruce, John, and Pat, I
think I now know how to implement a many-to-many relationship, at least one
way. Thanks!

Al

I yield (this is becoming a number of angels dancing on a pin-head
discussion).

Please help Al, as he proceeds to grope with the M:M relationships.

Ed Warren.

An Access primary key is unique. That is what makes it a primary key.
Regarding your point about an extra field adding considerably to the size
of
a large database project, for purposes of this forum I tend to assume that
people are working on relatively small projects. Somebody developing a
database to store terabytes of information or hundreds of millions of
records
is probably past the point of needing to ask about many-to-many
relationships.
I have noticed there is a long-running debate about a separate PK field
rather than a "natural" PK based on data in the record. All I will say on
that topic is that either approach is probably valid. I find that
autonumbers (or such things as Employee IDs or invoice numbers) work well,
have the advantage of not leaving me to wonder whether I have satisfied
the
"uniqueness" requirement, and are simple to implement.
Presumably each record in the junction table would contain additional
information such as year, model, color, etc., they are not really
repeating
records. A family with two children could well contain repeating
information
in some fields of a Children table, but that doesn't mean they are
repeating
records.
One other point has to do with cascading deletes. If you remove a person
from the database (I would be inclined to use a query to filter them out
of
the recordset rather than deleting their records) then it makes sense to
also
eliminate related records from the junction table. However, if you delete
a
record from the vehicle table and have cascade delete set up you would be
eliminating from the database that anybody had ever owned that type of
vehicle. That may not be what you intended.
I should probably study more about database theory so that I can jump into
discussions about first normal form and all that, but for now I am at the
point of asking "What if somebody owns two Fords?". Please don't get me
wrong. I think it's great that you have put so much time and thought into
your responses. That I can navigate databases now is due in large part to
people like yourself who were generous with their time and knowledge when
I
was getting started. I am just suggesting alternative thoughts on some
particular points. It is meant as a discussion, not a criticism or
anything
negative. I sincerely hope the spirit of my remarks is coming across as
intended.

Sometimes one must keep things simple. (Required in this case to try to
explain M:M relationships.

I come from a bit not stored is a bit saved background.

Not saving an additional field saves a few bits or couple of Bytes and
over
a large database them little bits/bytes can add up in search time and
file
size. They can get important in designs storing a several of terabytes of
data with a few 100 millon records.

Yes I could add a unique Key field, and that would be appropriate if what
I
wanted was to know how many jeeps one owned, but then I may want to add a
field to track the year of each, and/or I may want to put the number of
jeeps in another field e.g.
or maybe my VehicleType includes the model/year or ....... or......
or.....

PeopleID |VehicleTypeID| Year|numberowned
or
PeopleID |VehicleTypeID| numberowned
or
UniqueKey |PeopleID| VehicleTypeID
or
.......

Using a 'Unique Primary Key' is appropriate when it is required and
inappropriate when not.

I'm no expert with regard to the theory of database normalization, but
best
I can recall you must remove any 'repeating' rows, to get to third normal
form.
Your design would produce 'repeating rows', and while convienient and
simple
this deviates from the strick standard. When required, I certianly do
this
rather than build yet another table to tie stuff together).

E.g.
Key PeopleID VehicleID
1 1 2
2 1 2
3 1 2
4 1 3

Note rows 1,2,3 are repeating rows.


See all this is already too much for my feeble mind to grasp, gotta go
have
another cuppa coffee to wake up! ;>


Ed Warren.




You would run into problems with that combined primary key if one
person
owns
two of a particular type of vehicle. Given that some people are very
dedicated to a particular type of vehicle it is certainly possible.
The
combined PK is not guaranteed to be unique, and therefore is a
questionable
choice as a PK.

:

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.

 

[Pat Hartman]

3. The final grade is not always an aggregation of individual test scores so
it isn't always calculated. The teacher frequently has some leeway to apply
judgment. That means that the intersection of student, course,
semester/date will have only one final grade and this is the only table that
has the correct primary key to qualify the grade uniquely.

 

[John Vinson]

John,

Do I understand correctly that if I "popup" the other "one" side when adding
an item to the combo box values (because it is not currently in the list)
that I can only display values on the other "one" side but can't insert or
edit values? Is that because the forms aren't synchronized?

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]