Many to Many Relationships for Multiple Tables?

[RichNCSU]

I know enough about relational databases and Access to be really dangerous. My current project is to create a database to track contacts at different organizations. Each contact could have multiple roles (CIO, CEO, etc.) and could be associated to multiple organizations. To make this even more fun, these organizations could potentially share a parent organization. The contacts for the child organizations could also have roles in the parent organization. When I try to plan this on paper it starts to look like spaghetti.



Here are the tables I've got so far to handle the many to many for contacts and roles:

tblContact
pkContID

tblRole
pkRoleID

tblContactRole
pkContactRoleID
fkContID
fkRoleID

Here is what I have for the parent and child organizations:

tblParent
pkParentID

tblOrganization
pkOrgID
fkParentID

How do I relate the contacts with their potential multiple roles to multiple parent and\or child organizations?

Thanks in advance for your help...
Rich

[jzwp11]

Welcome to the forum!

A parent is an organization, so 1 table for all organizations

tblOrganizations
-pkOrgID primary key, autonumber
-txtOrgName

Now relate the parent organization to its children

tblRelatedOrganizations
-pkRelOrgID primary key, autonumber
-fkPOrgID foreign key to tblOrganization (the primary or parent)
-fkSOrgID foreign key to tblOrganization (the related secondary or child)

And of course relate the contact/role combination to the organization

tblOrganizationContactRoles
-pkOrgContactRoleID primary key, autonumber
-fkOrgID foreign key to tblOrganizations
-fkContactRoleID foreign key to tblContactRole

[RichNCSU]

Thanks for such a quick response jzwp11. I never considered that the parent and child organizations should be kept in the same table. This is so different than what I was thinking. Seems much more simple and elegant though.

So based on this structure, how would I set up the underlying query to create a report showing parent organizations with all of their children and the contacts for each one?

Rich

[jzwp11]

You need 3 queries. Rather than trying to explain, I decided to just give you an example. See qryFinal in the attached example database.

[RichNCSU]

Wow! This will save me a ton of time and much frustration. You are THE man (or woman as the case may be.) Thank you for taking the time to set me on the right path with this.

[jzwp11]

You're welcome!

jzwp11(aka Greg)

[orange]

I have attached the relationships diagram provided by Greg/jzwp11 for those who may be interested in the structures.

[RichNCSU]

So I really thought I had this one solved thanks to Greg's help. Conceptually it all works beautifully. Then, as it usually does, reality messed it all up! As I have started to pull the external data and create the database I'm running into some problems. Some of it is from not fully understanding all of the data I was pulling for import which was bad planning on my part. Here is a more detailed and informed description of my data.

ParentOrgs have a set number of OrgRoles (CEO, CFO and CIO)
ParentOrgs have 1 or many ChildOrgs
ParentOrgs have 15 field specific to ParentOrgs along with some basic fields shared with ChildOrgs (address and phone)

ChildOrgs have the same OrgRoles as ParentOrgs (CEO, CFO and CIO)
ChildOrgs have exactly 0 or 1 ParentOrgs
ChildOrgs have 12 statistics specific to ChildOrgs

Contacts have a title that may or may not correspond to their OrgRole
Contacts can fill multiple OrgRoles for multiple ParentOrgs, ChildOrgs or both
a Contact may have different OrgRoles in different Orgs (a CEO for a ChildOrg and a CIO for a ParentOrg)
Some OrgRoles are vacant

Here's what I have drawn up so far:
ParentOrgs
pkPOrgID
address, phone, etc.
unique ParentOrg fields

ChildOrgs
pkChildOrgs
fkParentOrgs
address, phone, etc.
unique ChildOrg fields

Contacts
pkContactID
address, phone, etc.
unique Contact fields

Questions
1- This is taking a different direction that what Greg suggested as I can't seem to make my data fit that mold. Am I on the wrong track?
2- How do I associate the contacts to the ParentOrgs and ChildOrgs based on the info above?

[jzwp11]

1- This is taking a different direction that what Greg suggested as I can't seem to make my data fit that mold. Am I on the wrong track?

I believe so.

ParentOrgs have 15 field specific to ParentOrgs along with some basic fields shared with ChildOrgs (address and phone)
ChildOrgs have 12 statistics specific to ChildOrgs

Can you provide more info on these fields/statistics?

[RichNCSU]

Good to know that I am still just looking at this the wrong way.

These are hospitals for what it's worth.

ChildOrgs
# of Beds
# of ER visits
# of Radiology Procedures
etc.

ParentOrgs
# of FTEs
for profit/not for profit
# of hospitals
# of long term care facilities
# of home health facilities
etc.

[jzwp11]

You should treat the statistical items as records not fields. Then tie the item(s) to the organization(s) to which they apply

tblStatisticalItems
-pkStatItemID primary key, autonumber
-txtStatItemName

tblOrganizationStats
-pkOrgStatID primary key, autonumber
-fkOrgID foreign key to tblOrganizations
-fkStatItemID foreign key to tblStatisticalItems
-longStatValue (the actual statistic ie. the #)

For the profit or non-profit, I would consider that a category to which the organization belongs. You do not have to specify it if you do not need to as in the case of the organizations that are not parent organization unless the child is different from the parent. (A for-profit child that is related to a non-profit parent)

tblOrganizations from my earlier design would now look like this

tblOrganizations
-pkOrgID primary key, autonumber
-txtOrgName
-fkCategoryID foreign key to tblCategory


tblCategory (2 records non-profit or for-profit)
-pkCategoryID primary key, autonumber
-txtCategory

If many categories (not just profit or non-profit) can be used to describe an organization (i.e. non-profit & religious affiliated ) then you would have a different relationship (i.e. many-to-many) which would look like this


tblOrganizations
-pkOrgID primary key, autonumber
-txtOrgName


tblCategory
-pkCategoryID primary key, autonumber
-txtCategory



tblOrganizationCategories
-pkOrgCatID primary key, autonumber
-fkOrgID foreign key to tblOrganizations
-fkCategoryID foreign key to tblCategory


Better yet...

# of hospitals
# of long term care facilities
# of home health facilities

If you have a parent organization and you capture & define each child organization as to its type: hospital, long-term care facility etc. in tblOrganizations, you would not need a record to hold the count, you would create a query that does the counting by type. You would not have to update the count records, only add new organizations as necessary and rerun the query to get current counts of each type--more dynamic.

[RichNCSU]

Fantastic work Greg. Thank you once again. Now to start building...

[jzwp11]

You're welcome.

[orange]

Good stuff, RichNCSU, would you please post a jpg of your relationships for others.