There's someimes need to have multiple one-to-many relationship between one (primary key) side and multiple (foreign key) sides in another table!
You have "A medical procedure table"!
Every medical procedure is performed by a doctor (foreign key in procedures tables and primary key in doctors table).
Suppose you have residents and specialists, residents don't have the right to sign the release papers, but you want to keep information about the fact that they performed a procedure.
So you would have signedby_DoctorNO and performedby_DoctorNO fields in procedures tables that both link to primary key in doctors table.
However Access doesn't seem to handle that well, because when creating a form viewed by procedures you never get the chance to have Doctors data displayed twice (for signedby and performedby doctor).
Maybe you could create many-to-many relationship where every procedure is performed by many doctors (in this case two - one signing it and one performing it) and documenting the function they have in a particular procedure but it simply seems contra-intuitive.
Or, you could argue that doctors who have signing rights and those who don't are subclasses of doctors and should be separated in two tables.
Or you could have two exact copies of doctors tables named Doctors and Doctors1 and creating two one-to-many relationships to procedures!
THIS IS A THEORETICAL QUESTION ON MY PART, I CAN HANDLE THE SOLUTION IN THIS PARTICULAR CASE. I WOULD JUST LIKE TO KNOW WHAT THE RIGHT SOLUTION IS (advantages, disadvantages of solutions mentioned above, plus propositions of some that I wasn't able to think of).
But I still have one real life frequently encountered problem where you need 2 one-to-many relationships between two tables.
Clubs and SportsEvents
Each Sport Event takes at least (and in most cases exactly) 2 clubs, home and away!
Lakers vs. Celtics
ManU vs. Liverpool
And it would be hard to argue that ManU and Liverpool don't deserve to be put in a single Clubs table. They are both clubs, only playing away or at home in a particular game!
No, but you can add the table twice to the form's underlying query, which achieves the same result. Even better in queries is that you can give each a different alias (name), so, for example, Team_1 could become TEAM_A and TEAM could become TEAM_B.