how to add data with many to many relationships

[Guest]

I am trying to use a many to many relationship to organize my data and am
having trouble entering the data. My database has three tables: Landowners,
Sites, and a junction table: Site Ownership. Landowners can own many
different sites and sites can have many different landowners. The tables are
like this with the primary keys indicated with an asterix; I have a multiple
primary keys for the Sites table for organizational purposes.

"Landowners" "Sites" "Site Ownership"
LandownerID*(autonumber) Site#* LandownerID(long integer)
Name Watershed* Site#
Address Comment Watershed
Soil type


The junction table has two one to many relationships established, one for
each table that feed it.

I import the Site table data first and want to use the database to assign
landowners to the sites. I have created a form based on the Sites table with
a subform based on the Landowners table to attempt to do this. It seems that
entering data this way does not work and does not update the junction table
(Site Ownership) automatically. When I alternatively try to add data to the
junction table I am given the error that I cannot add records to it because
related records are required in the other two tables. I do have some data in
this table but cannot remember how I was able to enter it. If there is a
possible solution I would like to be able to select the landowner from a list
(or using a combo box).

Thanks in advance for any help or ideas

 

[James Hahn]

If I understand your description properly, you aren't updating the sites or
landowners table. Therefore your form should be built around the Site
Ownership table. A record in this table simply indicates that an existing
site is associated with an existing landowner. You would create this record
by nominating the landowner and the site/watershed. The other two tables
wouldn't be involved, other than for validating the data entered.

The Site Ownership table will need a key that consists of the site and the
owner, unless you manufacture a separate key such as an autonumber.

The site table only needs the watershed as a key if the site ID is not
unique - if the site ID is unique you can remove the watershed as a key (but
you may want to create an index that uses it). This would simplify the Site
Ownership table..

 

[Sylvain Lafontaine]

Are you trying to create new Landowners and new Sites and enter them in the
Site Ownership at the same time?

Suggestion: add a primary key to the Site Ownership table and replace the
composite key with an autonumber as the primary key for the Sites table.
Access has trouble with composite keys and you should wait to have a deep
understanding of Access before using tables with no primary key or with a
composite key.

S. L.

 

[Guest]

If I understand your description properly, you aren't updating the sites or
landowners table. Therefore your form should be built around the Site
Ownership table. A record in this table simply indicates that an existing
site is associated with an existing landowner. You would create this record
by nominating the landowner and the site/watershed. The other two tables
wouldn't be involved, other than for validating the data entered.

The Site Ownership table will need a key that consists of the site and the
owner, unless you manufacture a separate key such as an autonumber.

The site table only needs the watershed as a key if the site ID is not
unique - if the site ID is unique you can remove the watershed as a key (but
you may want to create an index that uses it). This would simplify the Site
Ownership table..

 

[Guest]

Sylvain,

Thanks very much for your suggestion, it solved my problem and now I am able
to enter data just the way I want. I know that I can always count on
Quebecers! You are a great group of people. Encore, Merci Beaucoup.
Sincerely,
Chris