How to Link Tables in a Complex Many-to-Many Relationship?

Question

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

MarkShnierYou · Accepted Answer

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.

MarkShnierYou · Answer

.... and then if you are on Legacy forms put the Report Link field from the Relationship between Physicians and Physician Credentials on the form and set the form to show the detail. If you are on new forms, put a report of Physician Credentials on the form and set the report link field to filter the report. That way when you look at Physician you will see all the Credentials.

The do the similar steps on the Credentials record to see an embedded report of where the Physicians stands on their Physician Credentials.

Sign up

Login to the community