Retrieve a value from a table based on a join table value

[ianintheworld]

I'd be very grateful if someone could give me some advice on how to retrieve values from a related table where there is a match in the middle join table.

 

I have 3 tables:

 

Projects

ContactLink

Contacts

 

 

Projects and Contacts are joined to each other thru the ContactLink table.

 

 

In the Projects table I have a few fields for various contacts:

 

Architect

Contractor

Project Manager

 

 

I would like these fields to be populated with the values from the Contacts table where the Contact Role matches the field in the Projects table.

 

So if I define 'Contact X' as an 'Architect' I want the Projects::Architect field to be populated with 'Contact X's name.

 

Any thoughts on how to do that?

 

Thanks in advance!

 

 

 

 

[bcooney]

In the Projects table I have a few fields for various contacts:

 

Architect

Contractor

Project Manager

 

----

 

Why? Shouldn't the ContactLink table be a portal of people that are related to the project, each with a roleID. You also need a Roles table.

 

If you are looking for a way to "shop" for architects to relate to your project, then you should have a roleID in the Contacts table. Then you could use a popup list with a filter to show Contacts available for a certain role.

[ianintheworld]

I already have a form layout that shows a ContactLink portal of contacts and their roles.

 

The issue arrises when I try to show a list view of all Projects with their respective contacts in line. In the projects list view I have column headings for Architect etc and it's there that I need to have fields in the projects table that pull this info from the contacts table.

 

So in effect the ContactLink table is a portal of related people for each project.

 

I'm not trying to choose contacts for each project - that's already done. I'm trying to display each of them in a list view in the projects table.

 

Hope that makes sense?

[bcooney]

Sounds like you should base the list view on the contactlink table, and subsummary by project.

[Wim Decorte]

In the Projects table I have a few fields for various contacts:

 

Architect

Contractor

Project Manager

 

Echoing what Barbara said: these are attributes of a role and do not belong in the projects table.  The join between the project and the contact is your "roles" table.  So you need an extra field there to describe the role.  It also means that conceivable the same contact can have multiple roles in the project.

[bcooney]

Expanding on this...you may feel that a Contact can serve in more than one role. So, create a ContactRole table. This will allow you to assign a contact to several role types. For example, Bob is a "Developer" and "Business Analyst" and "Testor," he'd have three records in ContactRole.

 

ContactRole is the roles a person may perform on a Project (that is, those for which he's qualified). ContactLink (I would name this join table ContactProj) stores the role the contact is actually performing on the Project.