Tracking Project with Multiple Contacts

  • Thread starter Thread starter Mark B
  • Start date Start date
M

Mark B

I'm trying to put together a db to track projects. Each project can
have multiple contacts (i.e, a business contact, a technical contact,
a project contact, etc).

Here's my tables:
tblProjects
ProjectID
ContactID
ProjectName
Status
Comments
...etc...

tblContacts
ContactID
Name
Area

In the Contacts table, it looks something like this
Name Area
Linus Business
Marcie Business
Lucy Technical
Charlie Project

In a form I'd like to have dropdown/combo boxes for the separate
contact areas so the user can select names from a list. So there'd be
a combo box labled "Business" that would only disply "Business" names
(e.g., Linus and Marcie). Same idea for the other areas.

I've created a dropdown box using query that can do this, but problem
is, the field won't re-initialize when I move to a new record, and
won't save if I close out of the form and come back in.

What should be the control source and what should be the record
source?

Any ideas would be MUCH appreciated!

Mark
 
You'll also need tblContactType.

In a subform you'll be able to associate as many people as you need, and
their ContactType, with each project. Each field Person and ContactType can
be selected by a combo box.

Entities:

tblProject
ProjectID (PK)

tblPeople
PersonID (PK)

tblContactType
ContactType (PK)

Relationships:

tblProject-Contact
ID
ProjectID (FK)
PersonID (FK)
ContactTypeID (FK)

Any person can be a contact on any project, and can have more than 1
ContactType per project if desired. No restrictions.
 
Back
Top