Linking tables

[dhannant]

So I'm sure this is a very simple answer but I am having some serious issue. I've got 2 tables... tbl_employees and tbl_projects. They are both pretty obvious on what they contain.

Numerous people can be assigned to a project but there is only 1 Lead. So I assign individuals to a project and store said data on the employee table (tbl_employees.project). From there I want to assign the project the lead (tbl_project.lead)... In the lead field I have a drop down that shows employees from tbl_employee... I've gotten it to filter down to people assigned to a project however, I want it to only show those assigned to the project I'm currently working on.

For instance... on project "West Gate" at the "Lead" field, it should only show those that are assigned "West Gate" on field tbl_employees.project. Every type of join I've tried brings up either everyone on the employee table or anyone on the employee table that is assigned ANY project... how can I make it provide on specific ones?

 

[Ranman256]

You need 3 tables....tEmps,tProjects,tEmpProj to hold what EMP is on what project.
I would also add a LEAD field to tProjects.

TEmpProj table:
EmpId
ProjID

 

[The_Doc_Man]

To amplify what ranman said (and he is absolutely right, by the way), look up "junction tables" to understand what he is talking about.

 

[dhannant]

Thank you both for the information... not only did it do what I wanted I was able to add on additional features like when the project has been reviewed by which people... not a feature needed as I was just playing around with an idea but hey... I know how now if I wanted to do something along those lines.

I appologize for the late response but it took me a minute to figure out how to integrate it properly and then ofcourse holidays. Again... thanks a bunch!!