Counting records with a complicated multi table relationship
I am not sure if what I am asking for is possible.
I have a 'name' table of physicians where one of the fields is a board certified specialty. Included on this table is another field if they are a member of our organization.
We hold an election where we merge many specialties into a summary specialty that combines various similar specialties into one specialty name that is broad enough to represent these many specialties. In other words we might have 130 different specialties that we merge into maybe 60 different ones.
I created another table, 'conversion', that is a conversion table that states a certain specialty is the merged specialty
I want to have a layout of ones t a time a merged specialties that shows in a portal, all the physicians who are board certified in a particular merge specialty. I got that portal to work perfectly.
Here is my problem. At the top of this layout, I want it to count how many of these folks are members and how many are not.
I can get a total of everyone who is board certified, but I can't get the count to 'find' the physician through the conversion table and count the member? field on the name table.
If I am making any sense and anyone has any suggestions, it would be appreciated.