How do I set up the relationships so that I can assign a child record in the parent form and then see the related parent record in the child form?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I have a parent table named Clients and a child table named Staff. Each Client has 5 Staff Members assigned to them in different roles (the 5 roles have separate fields in the Client table). I want to be able to assign Staff Members to each role in the Client form from a drop down list (from the Staff table). Then I need to be able to see all of the assigned Clients each Staff Member has on the Staff form. I've created both the One Client to Many Staff relationship and the One Staff to Many Clients relationship but I can't see a drop down list of Staff Members in the Client form in order to assign them. Also, once I am able to assign the Staff Members to the Clients, how do I show a report of all the related Clients on the Staff Member form?

Photo of John


  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of Xavier Fan

Xavier Fan, Champion

  • 630 Points 500 badge 2x thumb
Sounds like you'd actually need a Many-to-Many relationship between Clients and Staff.

So you'd have the following 3 tables:

1)  Clients

2)  Staff

3)  Client-Staff Assignments - this table would have a field called [Role] (most likely a text dropdown)

With the relationships as follows:

1)  1 Client has many Client-Staff Assignments

2)  1 Staff has many Client-Staff Assignments

When you add a new Client-Staff Assignment, you'd select the [Client] and the [Staff] from dropdowns, and also the [Role].

The Client form would contain an embedded table of Client-Staff Assignments - showing the Staff assigned to that Client, and the role.

The Staff form would contain an embedded table of Client-Staff Assignments - showing the Clients that this Staff member is assigned to (showing the role would probably be helpful as well).

You may be creating reports in any of these 3 tables, depending on the exact need.

For reference, here's the online help on Many-to-Many relationships: