使用访问来填充多对多表 [英] populate a many-to-many table with access

问题描述

我有两张表（个人和项目），这些表在多对多表中，因此通过第三个表链接在一起 persons_projects

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

在ms访问中，我现在创建了一个表单，显示来自 project 表。

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.

我该怎么做？我可以显示所有的参与者，但是我无法添加它们。似乎我再次出现插入视图问题，因为我需要个人和 persons_projects 来显示正确的数据集。但是，因为我只是在单个表格中更改/添加行（ persons_projects ）我不知道为什么访问权再次发生。

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.

推荐答案

你不应该需要人，只需要person_projects。我假设people_projects包含：

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.

子窗体设置为project_id的Link Child和Master Field，将由Access自动完成，还有一个类似于

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 

编辑注释

尽管经常有一点困难，可以包含两个表，并具有可更新的记录集，查询（视图）应该包含来自联结表的project_id和person_id。

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.

这篇关于使用访问来填充多对多表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！