How to Link Tables in a Complex Many-to-Many Relationship?
Hello all,
I have two tables:
- Physician Info: Contains columns for the physician's name, person number, and other static info about that physician.
- Credentialing: Contains the physician's name along with approval and renewal dates for various credentials.
The reason I’m not combining everything into one table is that the Credentialing table is extensive, and I plan to add other tables in the future as well and if it is all in one table it becomes massive to edit.
I know I need a Join Table to link these two tables. In the Join Table, I want to click on a physician's name and see all the information from both the Physician Info table (name, person number, and static info) and the Credentialing table (approval and renewal dates).
I’ve watched the online many-to-many video multiple times and even have a simpler app that uses a many-to-many relationship, but I can’t seem to wrap my head around how to apply it to this project.
How do I properly set up the Join Table to link these two tables, and how can I make it so I can view all relevant information for each physician in one place?
Thanks!
Meag
np,
This sounds like a vanilla situation.
You already have a table of physicians.
Make a new Table of Credential Masters.
Then make a table for Physician Credentials as the Join table.
Make a relationship where 1 Physician has many Physician Credentials and look up the Physician name and go to the field for Related Physician and set the Physician Name as the Proxy.
Make a relationship where 1 Credential Master has many Physician Credentials and look up the Credential name and go to the field for Related Credential Master and set the Credential Name as the Proxy.
Then add fields on the Physicians Credentials table for your various approval and expiry dates and any comments.
I think you biggest issue will be to un-clutter your app with what you have built to date.
