Re: Many-to-many relationships

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


"Al Williams" <AlWilliams@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:80A7FEBB-7D2F-4D66-B99D-C7B67E437F54@xxxxxxxxxxxxxxxx
> 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


.

Relevant Pages

  • Re: Many-to-many relationships
    ... are done to setup the combo boxes. ... > Each 'vehicletype' can be owned by many people. ... > PeopleID VehicleTypeID We make the combination PeopleID; ... > relationship integerity, auto update, autodelete) ...
    (microsoft.public.access.gettingstarted)
  • Re: Many-to-many relationships
    ... two initial tables as primary keys in the junction table as well as in the ... Each 'vehicletype' can be owned by many people. ... PeopleID VehicleTypeID We make the combination PeopleID; ... relationship integerity, auto update, autodelete) ...
    (microsoft.public.access.gettingstarted)
  • Re: Many-to-many relationships
    ... They are primary keys in what you are calling the ... PKs are designated as such in table design view; ... Each 'vehicletype' can be owned by many people. ... PeopleID VehicleTypeID We make the combination PeopleID; ...
    (microsoft.public.access.gettingstarted)