Hi,
I have a database which contains a many-to-many realtionship and I understand that I have to create an intersection table between the two tables so that I have two one-to-many relationships instead. This intersection table only contains the two primary keys of the tables that they are linking.
e.g.
Sites table - primary key: Site Number
Reports table - primary key: Report Number
A site can belong to many reports and a report can have many sites so I set up an intersection table: sites_report
However, I am unsure as to how this intersection table is populated. When a user enters a new record for the sites table for example, how is the site number (an aplhanumeric code) put into the intersection table?
I understand that autolookup will insert values from the many side of a table into the one side, but in this case I am trying to populate the many side. As well as this I have tried using subforms but when I enter a site number for the intersection table I am told that a corresponding value does not exist in the sites table, which it doesnt. I have also attempted to use queries (append and make table) to no avail.
I am sure that there is some fundamental element that I must be missing.
Can anyone help me? If you can I would be really greatful as I've been working on this for 3 days now and Im getting nowhere.
Thanks,
Helen
I have a database which contains a many-to-many realtionship and I understand that I have to create an intersection table between the two tables so that I have two one-to-many relationships instead. This intersection table only contains the two primary keys of the tables that they are linking.
e.g.
Sites table - primary key: Site Number
Reports table - primary key: Report Number
A site can belong to many reports and a report can have many sites so I set up an intersection table: sites_report
However, I am unsure as to how this intersection table is populated. When a user enters a new record for the sites table for example, how is the site number (an aplhanumeric code) put into the intersection table?
I understand that autolookup will insert values from the many side of a table into the one side, but in this case I am trying to populate the many side. As well as this I have tried using subforms but when I enter a site number for the intersection table I am told that a corresponding value does not exist in the sites table, which it doesnt. I have also attempted to use queries (append and make table) to no avail.
I am sure that there is some fundamental element that I must be missing.
Can anyone help me? If you can I would be really greatful as I've been working on this for 3 days now and Im getting nowhere.
Thanks,
Helen