Complex relationships

[samiuweb]

Ok,
let me see if I can explain my situation.

I have a database that deals with community-based projects (tblProjects) which are linked in many-to-many relationships with Organizations, Students and Professors. I got the whole architecture of this thing sorted out, but something came up.

It turns out, that now Organizations can have more than one contact. But only one contact can be linked to a project. So I'm guessing that I will need to have a table for Contacts, but I just can't figure out how to make the connections b/w Contacts, Organizations and Projects.

Taken separately, Organization is 1 to many with Contacts, Projects are 1 to many with Contacts and as initially set up Projects are many-to-many with Organizations.

What i was also finding trouble with, was that I need to have organization and contact side by side in any forms or reports, so they would have to be linked in some way.

Help much appreciated.
Penar

 

[Pat Hartman]

Projects are 1 to many with Contacts

- this is the opposite of what you said here -

But only one contact can be linked to a project

If Organizations is many-to-many with projects, that makes projects many-to-many with contacts but with the qualification that each organization has only 1 contact for a particular project. So this changes the junction table that makes the many-to-many between Organization and Project. You need to add a third field to contain the ContactID:

tblOrgProj:
OrganizationID (pk fld1) foreign key to tblOrganization
ProjectID (pk fld2) foreign key to tblProject
ContactID foreign key to tblContact

 

[Randomblink]

Eh?

Ok...
If I am understanding you correctly...

I do something similar...

I create the following:

---------------------
tbl_Projects
---------------------
prj_ID [Autonumber - Primary Key]
prj_Name
prj_etc, etc, etc...


---------------------
tbl_Consultants
---------------------
cns_ID [Autonumber - Primary Key]
cns_Name
cns_etc, etc, etc...

THEN... the clincher here is...

I create ONE more table...
I call it...


---------------------
tbl_LINK_ConsultantToProject
---------------------
cns_ID [Number - Primary Key]
prj_ID [Number - Primary Key]

Then, under relationships, I link them...
This way...
you have a consultants table...
that can only have ONE consultant per ID number...
AND you have a projects table...
that can have only ONE project per ID number...
AND You have a link table that will allow any consultant to be connected to any project... BUT only once...

If you have any questions, I can send you an example of a data app that I use at work with many tables and many link tables... it works for me...

Later

 

[samiuweb]

thanks pat
i understand your solution. but then how would i go about showing this into forms and reports, is there another place i could look into what someone lse has already done before? im having trouble with the terminology for this.

pm

 

[Pat Hartman]

Follow this link to the many-to-many sample db that I posted a while ago. It should help you to understand many-to-many relationships that have intersection data (data fields in the junction table).

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=40821&highlight=manytomany

 

[samiuweb]

hello again,
i have seen the db you forwarded me to, and i feel that what i am looking for is more complex.

so again, lket me try and explain. so we have organizations that might have multiple contacts (eg. YMCA New York has contact person 1, YMCA Bonx has contact person 2). From one Organization, there is 1 contact supervising a project at a time, but there might be multiple organizations linked to a project, each with their own contacts.

In a form I want the user to choose the affiliated organization for a project (this is working right now, done through a subform) and then in a second combo box i want the user to choose the appropriate contact.

So, there would have to be some way of linkinf contacts to organization, and then to project.

From the database i would also need to have a mailing list of all the contacts and their respective organizations (regardless of project).

The database is already working well, and it already has a bunch of many-to-many reationships. Students are linked to projects in many-to-many. Professors similarly. At the time, organizations too, but there is currently no accomodaiton for multiple contacts.

Is it any clearer now?

thanks pat,
penar

 

[samiuweb]

an idea:

why don't I just link Projects and contacts on many to many, and then when from the como box the contact is selected, the appropriate organization shows up? If this is good, how would i go about doing it?

thanks
penar