populate a many-to-many table with access

Question

I have two tables (persons and projects) which are in a many-to-many table, thus linked together by a third table persons_projects

In ms access I now created a form showing data from the projects table.

What I want is to have a subform showing all persons- datasets which participate in this project. In this subform it should also be possible to add (or delete) persons from this project—a drop-down seems the best choice here.

How can I do this? I’m able to show all participants, but I’m not able to add them. seems like I have the “insert into view” problem again, since I need persons and persons_projects to show the correct datasets. but as I’m only changing/adding rows in a single table (persons_projects) I don’t see why access is bitchy again.

Answer 1

You should not need persons, only persons_projects. I assume that persons_projects consists of:

person_id  -> FK  ) Combined as PK, perhaps, if not, an autonumber PK
project_id -> FK  )

and (recommended) a datetime stamp and user field.

The subform is set-up with a Link Child and Master Field of project_id, which will be automatically completed by Access, and a combobox similar to:

Control Source: person_id
Row Source: SELECT person_id, surname & " " & forename, some_field FROM persons
Bound Column: 1
Column Count: 3 
Column Widths: 0cm;2cm;2cm

Edit re Comments

It is possible, though often a little more difficult, to include both tables and have an updatable recordset, the query (view) should include both project_id and person_id from the junction table.

Answer 2

Sounds like persons is the driving dataset here since you want to be able to link it to multiple projects as well as delete the person record.

Base your form on the Persons table. A subform should be based on the person_projects table and linked by the corresponding id's. You can use a combo box on the projectid in the subform and have some other field displayed so the user can identify the project (name?). You may want to show all the project data for each project on this form, but you may find it getting very confusing to the user. Having a separate form that you can 'pop-up' to give more project information may be a better choice.

If you want to delete a person, you can just delete from the persons table, but you need to decide if you want any orphan records in the person_projects table (Which you shouldn't). It's easy in Access to establish a link with referencial integrity (cascading update and delete optional). It's up to you as to how robust this needs to be.