many to many relationship

[Jose]

Is it necessary to have a linking table for the following tables? I seem to
be doing fine without it... Even though I do have it...

Lease Table
Well Table

LeaseID in Lease table relates to LeaseID in Well Table.

I have a linking table using PKs of the above tables for a PK.

All of my forms are ignoring the linking table (not placing any values in
them after i create new wells and leases) but the relationships are still
being enforced.

For example when I go to my wells table it shows the lease id that it
belongs to.

suggestions?

thanks

 

[John W. Vinson]

Is it necessary to have a linking table for the following tables? I seem to
be doing fine without it... Even though I do have it...

Lease Table
Well Table

LeaseID in Lease table relates to LeaseID in Well Table.

Does each Well have one and only one Lease? Or might a Well have multiple
Leases (and a Lease covers multiple Wells)? Only in the latter case do you
need a third table.

I have a linking table using PKs of the above tables for a PK.

All of my forms are ignoring the linking table (not placing any values in
them after i create new wells and leases) but the relationships are still
being enforced.

Why would you expect it to do so?

You have a table with 56 wells.
You have another table with 84 leases.

Which lease goes with which well? How could Access possibly know, unless you
enter data into the junction table yourself?

For example when I go to my wells table it shows the lease id that it
belongs to.

suggestions?

If each Well has only one lease, get rid of the junction table completely -
the "foreign key" LeaseID in the Wells table will do all that you need. You
can create a Query joinig the two tables on LeaseID in order to see both well
data and lease data together (for a Report let's say).

I think the problem is not the way Access is working, but perhaps with your
expectations; what do you expect the relationship to DO (other than what it is
designed to do, prevent assigning a nonexistant lease to a well)?

John W. Vinson [MVP]

 

[Jamie Collins]

If each Well has only one lease, get rid of the junction table completely -
the "foreign key" LeaseID in the Wells table will do all that you need.

There is a design principle that a table either models an entity type or a
relationship between entities but never both. Is LeaseID an attribute of a
well? Sounds to me like it is not. Maybe not every Well is available for
leasehold (e.g. those with a FreeholdID <g>). When you are defining a
one-to-one relationship of Marriages between Persons (such one-to-one
relationships being very rare, I hear <g>), does the man get the
'WifePersonID' field or the woman get the 'HusbandPersonID' or both?

Rather, I'd say if each Well has only one lease then keep the junction table
(a.k.a. relationship table) and put a unique constraint on the Well key in
this table.

Jamie.

--