AnsweredAssumed Answered

Joining Multiple Tables in Multiple Ways

Question asked by aabrown5 on Feb 2, 2016
Latest reply on Feb 3, 2016 by aabrown5

Hello, thank you in advance for reading through this post!


I have three items I need to connect in multiple ways:

1. Staff

2. Program

3. Office


The relationships:

Office--->Staff  (1 to many)

Program <--->Office (many to many)

Program <--->Staff (many to many)


I want to be able to view:

1. Related programs & office spaces when viewing staff

2. Related staff & programs when viewing offices

3. Related office & staff when viewing programs


I currently have:

1. Staff TO --- Staff_Program_JOIN TO --- Program TO

2. Program TO --- Program_Office_JOIN TO --- Office TO


But I can't seem to get Staff --> Office. I've tried numerous ways, and I either end with multiple join records between staff/programs/office (Jane Doe shows up twice in programs, because she's linked to the programs through both the Staff and Office tables) or FM says the tables aren't related.


Attached are two ways I tried to solve the problem. I feel like this should be a pretty easy fix, but I've spent the last couple days attempting to sort it out, and I can't! What am I missing?