Hello, thank you in advance for reading through this post!
I have three items I need to connect in multiple ways:
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?