Many-to-Many Relationships 1

[MsHealy]

I'm new to Tek-tips and I must say I'm empressed by what I've read in here. I tried searching for information on many-to-many relationships and came up empty.

I need to create a third table for a many-to-many relationship but I not sure how to do it.

I have 5 Regions in our Bureau who are served by Community Rehab Providers(CRPs), yet these CRPs serve more than one region.

Region is a one field table. (simple)
Region I
Region II
Region III
etc.

The CRPs are in a table called "Organizations" which is my main table that I like to "form" around. If you know what I mean. I have other tables steming (or is the term children) from this table like: addresses, contacts, program services, etc.

I would like to have a subform in my Organizations Form to add all the Regions that one particular CRP services like I do for the addresses and contacts. Can I do this and how do I create the third (problem child) table?

Jackie

 

[fneily]

First hard to believe you couldn't find any info on many to many relationships in tek-tips. This is a common problem.
You have a region that can have many crp's and a crp that services many regions. So you have one table with your regions and info and another table with your crp's and their info. Create a "junction table". This junction table will have the primary key of your Region table and also have the primary key of your Organizations table. Within this table, any data common to BOTH of these fields will be placed. E.G. Cost of service - a region is charged a certain rate by a certain crp. The two fields, primary key of regions and primary key of organization, will be made a multi-field primary key of the "junction table". So a query will look like this:
RegionTable <-----> JunctionTable <------> OrganizationTable

Neil

 

[willir]

MsHealy

If you are still having problems, check out...
thread700-628486

 

[MsHealy]

I don't know what you mean by &quot;check out...&quot;

But anyway, I got the table set up and have been trying to add a subform into my main form. When Neil mentioned cost of services it dawn on me that the cost could be for different regions. So, I have been trying to link the program services with the regions so I could add a subform that I could click on a combo box to pick a region with it's own subform to add the services. But with the many-to-many table in between I'm not sure how to do it.

I have a main form for the organizations with a subform for addresses related to another subform for their contacts. I would like to do the same next to it for the regions. At one point it told me &quot;I can not add or change record because... It wasn't relating to my main Table &quot;Organizations&quot; How to I do this?

I haven't got back to you because I found one of Jeremy's messages that refer to an article on &quot;Fundamentals of Relational Database Design&quot;. I thought I was pretty good at this but there is still a lot to learn.

Jackie

 

[willir]

Jackie

I am very glad you have got things going, and yes Jeremy's article on database design is great.

I am new to this forum too, but I believe people can flag a presented solution as having helped by marking it with a &quot;star&quot;.

I did not respond to your query a few days ago because fneily's reply does provide a solution. When I so no further activity, and no &quot;star&quot;, I just added a link to another design issue that may have helped with your situation.

Good luck with your project.
Richard

 

[fneily]

Maybe I can clarify the &quot;junction table&quot; and how to use it. There's a Many-to-one relationship between the Region table and the junction table. There's a Many-to-one relationship between Organizations and the junction table. So your connection would look something like this:

Region Table Junction Table Organization
RegionTblPrimKey ----> RegionTblPrimKey
OrganiTblPrimKey <----OrganiTblPrimky
Common Fields

So one region (West) is connected to the junction table's region (West) which has as a second part the organizations servicing it (a Crp). The other way, a Crp is connected to the junction's table's Crp which has as a second part all the Regions (West, North, etc.) it services.

So you don't have a many-to-many relationship anywhere, but you have two many-to-one relationships.

Not an easy concept.

Neil