Re: Many-to-many relationships

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.

"Ed Warren" wrote:

> 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
    ... That is what makes it a primary key. ... > a large database project, for purposes of this forum I tend to assume that ... A family with two children could well contain repeating ... > record from the vehicle table and have cascade delete set up you would be ...
    (microsoft.public.access.gettingstarted)
  • Re: Many-to-many relationships
    ... That is what makes it a primary key. ... > database to store terabytes of information or hundreds of millions of records ... A family with two children could well contain repeating information ... > record from the vehicle table and have cascade delete set up you would be ...
    (microsoft.public.access.gettingstarted)
  • Re: Designing Block
    ... Vehicle make, model and MFG ... >> Vehicle RegNo tbl: ... > primary key matches a registration number, ... > plan to send letters or packages to a supplier, but maybe not otherwise, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: how do i set up a vehicle maintenance database?
    ... ModID (Autonumber, Primary Key) ... Other stuff that only applies to that particular vehicle ... Other tables may also be needed - the car people here and you yourself can ...
    (microsoft.public.access.gettingstarted)
  • Re: Many-to-many relationships
    ... That is what makes it a primary key. ... database to store terabytes of information or hundreds of millions of records ... A family with two children could well contain repeating information ... record from the vehicle table and have cascade delete set up you would be ...
    (microsoft.public.access.gettingstarted)
Loading