Question on relationships between project and POC tables?

[kdirvin]

Working in Microsoft Access 2007. I have two tables, a table listing projects and one listing POCs.
tbl_Projects
Project Number
Project Manager
Resource Manager
tbl_POC
POC ID
POC First Name
POC Last Name
The Project Manager and Resource Manager fields from tbl_Projects are set as Lookup fields from tbl_POC, where the names of the Project and Resource Managers are stored.
I want a query where I select a contract number and the first and last names of the Project Manager and Resource Manager display. When I put the Project Number, Project Manager, and Resource Manager fields into a query, selected a Project Number, and ran it, no records returned. When I then tried putting Project Number, POC First Name, and POC Last Name into a query, again no records returned.
Have a feeling the problem is table design. Have worked so long on this I have lost objectivity. Help!
Thank you!

 

[jzwp22]

First, even though Access has the capability of having lookup fields at the table level, it is not good practice to use them. Check out this site for more details as to why they are not a good idea.

Have a feeling the problem is table design.

Although the query not returning records can be solved, you still have a design issue. If more than 1 person is associated with a project, then you have a one-to-many relationship which requires a separate but related table.

Some general recommendations:
1. It is best not to have spaces or special characters in your table or field names
2. Do not use Access reserved words as table or field names.
3. Do not use lookups at the table level; they are best left for forms

One of my personal preferences is to have an autonumber primary key field in each table. In general, the primary key field should have no significance to the user; it is just a way for Access to uniquely identify the record.

tbl_Projects
-pkProjectID primary key, autonumber
-ProjectNumber


Project Manager
Resource Manager

tbl_POC
-POCID primary key, autonumber
-POCFirstName
-POCLastName

tblProjectPeople
-pkProjPeopleID primary key, autonumber
-fkProjectID foreign key to tbl_Projects (field needs to be a long number integer field)
-fkPOCID foreign key to tbl_POC (must be a long number integer field)
-fkRoleID foreign key to tblRoles (must be a long number integer field) corresponds to the role the person plays on the project

tblRoles (2 records: Project Manager, Resource manager)
-pkRoleID primary key, autonumber
-txtRole

 

[kdirvin]

Thank you very much for your thorough response and the helpful tips. The “tblProjectPeople” table makes sense….looks like a juncture table to accommodate the many-to-many relationship between tbl_Projects and tbl_POC. I am not 100% certain on how I would populate this juncture table. I’m thinking a main form based on tbl_Projects and a subform based on a query of tbl_POC, tblProjectPeople, and tblRoles?
My second question: if certain POCs get populated as my Project Manager and Resource Manager in tbl_Projects, why do I need tblRoles listing those roles? Won’t the fact that they are in my Project Manager and Resource Manager fields be “enough” to designate them as such?
Thanks again.

 

[jzwp22]

I am not 100% certain on how I would populate this juncture table. I’m thinking a main form based on tbl_Projects and a subform based on a query of tbl_POC, tblProjectPeople, and tblRoles?

Yes, your main form would be based on tbl_Projects, your subform would be based on tblProjectPeople. You would use a combo box tied to tbl_POC to populate the fkPOCID field. Likewise you would use a combo box based on tblRoles to populate the role the selected person will play on the project.

My second question: if certain POCs get populated as my Project Manager and Resource Manager in tbl_Projects, why do I need tblRoles listing those roles? Won’t the fact that they are in my Project Manager and Resource Manager fields be “enough” to designate them as such?

You will not have project manager and resource manager fields. You will have 2 records--one for each person and tied to each person will be their role (fkPOCID and fkRoleID respectively)

 

[kdirvin]

IT WORKS.

Literally shouted for joy. Thank you for explaining everything so clearly!!!

 

[jzwp22]

You're welcome!