many contacts to many projects

  • Thread starter Thread starter MeSteve
  • Start date Start date
M

MeSteve

What is the best way to set up my database? I have many projects that can
have many contacts; many contacts can be associated with multiple projects.
My first attempt at relating was following this:

http://allenbrowne.com/AppHuman.html,

but that was a little more than I think I need, or too much for my little
brain to wrap around.

Thanks.
 
Steve,
tblContacts ....
ContactID >> Primary Key
LastName
FirstName
................


tblProjects .....
ProjectID >> Primary Key
ProjectName
ProjectDate


tblContactProject ....
ContactID >> Foreign key from tblContacts
ProjectID >> Foreign key from tblProjects


The table tblContactProject is a joining table between tblContacts and
tblProjects.

Jeanette Cunningham
 
That is exactly what I have, I just have no idea how to use it from there. I
think I am confusing myself, this may be related, but may not be. I want to
be able to link contacts to projects (done) and some of those contacts I want
to be segregated as being the project engineers, and I think that is where I
am running into trouble.

How do I segregate some contacts to be associated with the project, not show
up on the project contacts list, but on the project engineers list? (And
still use the same master contacts table)
 
Steve,
You will need all contacts associated with a project to show up in
tblContactProject.
Put a field called ContactType in tblContacts.

When you want to see the engineers, use a query that selects all ContactID's
from tblContactProject for the ProjectID and include the ContactType field
from tblContacts. The criteria will be both ProjectID and ContactType =
"engineer"

It might be more useful to have a table called ContactType.
ContactTypeID >> Primary Key
ContactTypeDescr

Then tblContacts has the field ContactTypeID instead of ContactType

Jeanette Cunningham
 
The only problem I see with that is a contact might have 2 different roles on
2 different projects.
 
I suppose it is not impossible that a contact could have more than one role
in the same project? We may have Many-Many between Contacts and Projects,
Many-Many between Contacts and Roles. We could have a junction table for
ContactRole.

tblContacts ....
ContactID >> Primary Key
LastName
FirstName

tblProjects .....
ProjectID >> Primary Key
ProjectName
ProjectDate

tblContactProject ....
ContactID >> Foreign key from tblContacts
ProjectID >> Foreign key from tblProjects


tblRoles ....
RoleID >>Primary Key
RoleDescr

tblContactRole
ContactID >> Foreign key from tblContacts
RoleID >> Foreign key from tblRoles

Jeanette Cunningham

blem I see with that is a contact might have 2 different roles
 
Here is a description for searching, you can adapt it for data entry.

Use a main form (single view) for one of the Many side tables. Within the
main form, use a continuous subform bound to the junction table with the
Link Master set to the main form's PK field. This subform has at least one
visible control that is a combo box bound to the second table's PK field.
This form's Current event and the combo's AfterUpdate event sets an
invisible text box on the main form to the value of the combo box.

Along side the continuous subform is another subform bound to the other many
side table with its Link Master property set to the invisible text box.

With this arrangement, you can navigate or search for the main form record.
The continuous subform displays the items assigned to the main form record.
Clicking on a record in the continuous subform automatically displays the
related record in the other subform.

Jeanette Cunningham
 
Should I put a ProjectID field in the tbl_ContactRoles to specify which
project that person is in that role?
 
No, the table tblContactProject will keep track of which contact with which
project.

Jeanette Cunningham
 
On second thoughts, you are probably right. It would be a good idea to
repost this part of the question as a new post and let others put in their
opinions.

Jeanette Cunningham
 
Here's what I came up with so far. Seems like it will work to me. I just
combined the tbl_ContactRoles and tbl_ProjectContact into one because I
couldn't see how to make the jump all the way down the relational string from
projects to roles. And, It seems that will be easy to specify which contact
is in what role for what project.

tbl_Contacts
ContactID >> Primary Key
LastName
FirstName

tbl_Projects .....
ProjectID >> Primary Key
ProjectName
ProjectDate

tbl_ContactRole
ContactID >> Foreign key from tblContacts
RoleID >> Foreign key from tblRoles
ProjectID >> Foreign key from tblProjects

tbl_Roles ....
RoleID >>Primary Key
RoleDescr
 
Back
Top