Help Me Set Up Related Tables?

[DA_SCC]

Hi there, I'm hoping someone can help me figure out how I need to link these tables to make them work together the way I'm trying to achieve.. I started working on this last week but after watching so many videos and reading the Microsoft Learn pages, I feel I've hit a wall.

What I'm trying to accomplish is I have three tables that I need to relate to each other:

• Tables: Students, Staff, and Location.
• Rule: Each staff can be assigned multiple students
• Rule: Each student can only be assigned to one staff.
• Rule: Each staff can be assigned to multiple locations
• Each student can only be assigned to one location.

I want it so that when a user changes a student's location or staff assignment, it will update both columns on the student's record.

Right now I've set up a relationship Students and Staff - it is a N:1 relationship with Staff being the many and students being the one. Not really sure what I'm doing beyond this.

Ideally the end result I'm looking for is that when I go to the "Student" form to update a student's info, I can change either the "Location" or "Staff" column and both will update simultaneously, if that makes sense. (e.g. if I update John Doe's staff to be Jane Doe, this will also update John Doe's location, because Jane Doe is the staff at that location.. or vice versa) They are both lookup columns (Location and Staff).

The way things are set up right now, I have to change both columns on the form manually - they're not "shaking hands."

Thanks in advance!

[dpoggemann · Accepted solution]

Hi @DA_SCC ,

Based on the information you provided above, I think you are going to want a data model similar to the one below:

The Staff Location table will help you to identify the staff to each location (assuming you have multiple staff that can be at each location)?  You could do a N:N relationship as well and remove this table if desired but I like to usually have this intersect table so I can create additional metadata on the relationship...

Details:

1. Student table should have 2 lookups, 1 to Staff and 1 to Location
2. Staff Location table should have 2 lookups, both required, 1 for Staff and 1 for Location

Based on the relationships you identified, it is not really valid to do the following:

• Change the Staff member assigned to the student automatically changing the location for the Student.
  • The reason this is not valid is that the Staff member can be assigned to multiple locations so there is no way to determine which location the Student should be changed to.
• Change the Location assigned to the student automatically changing the Staff Member for the Student.
  • Similar to the first situation, there can be multiple Staff assigned to the location so you can not just change the Location on the student and have this change the Staff member

I would be happy to help you work through this model and the business case but these relationships to hopefully help you accomplish your needed design.