Not sure where the problem is....

[LLB]

I did try to search an answer for this but I wasn't quite sure just what to search. I'm not even sure what catergory to post under so I'll try here. Please bear with me as I try to explain this...

I am relatively new to access and I inherited this DB and been asked to modify it. I have a projects data base, two of the tables being tblProjectData and tblEmployee. The tblEmplyee has 3 fields; EmpID, LastName and FirstName. The tblProjectData had a field for EmpID. So far simple. I have been asked to make it possible to assign up to 3 employees to each project. I added 2 fields to the tblProjectData being EmpBID and EmpCID. To the data entry form I added two more combo boxes that reference the tblEmployee to populate the 2 new field on tblProjectData and it all seems to be working fine.

Where I'm getting stuck is when I try to put EmpB and EmpC onto a report. I can't seem to get it to pull off the tblEmplyee the first and last names. I assume it is because new ID's are not referenced on tblEmployee. Do I need to add EmpBID and EmpCID to the tblEmployee as well, and if so do I assign the same ID numbers as the original ID field since those are the numbers used to populate the fields on tblProjectData? How do I set up the relationships?

Maybe I'm looking at this the wrong way. Any help would be appreciated.

Lori

 

[theprez]

This is a table design problem (normalization). You need a third table that has atleast two fields. The table would be tblEmpProj. The fields would be ProjID and EmplID. You could add other fields here like date assigned etc...

To limit 3 employees to a project, you will need to edit check in the form you are using to only allow 3 people to be assigned to a project. The new table would look like this:

Project1 EmpaID
Project1 EmpbID
Project1 EmpcID
Project2 EmpaID
Project2 EmpbID

Using this table, you can get all the project data from tblProjectData and all the employee data from tblEmplyee. Also there should be relationships built between the new table and the other two tables and you will have a concatenated key (projectid and empid) for the new table.

 

[LLB]

I guess I'm still not getting it....

I created a new table tblEmpProj with the fields ProjectID, EmpID, EmpBID and EmpCID.

I joined the ProjectID field in the new table to the same in tblProjectData and joined the EmpID to the same in tblEmployee.

I ran a query to test and when I use EmpBID from the new table and LastName from tblEmployee I still get the EmpA information.

There is still no relationship joined to EmpB or EmpC id's. Not sure how to do this.

 

[theprez]

OK let me try to explain this. Do not define 3 separate employee fields in the table tblEmpProj. It will ONLY have two fields, a projectid and an employee id. In this table, there will probably be three rows for each project. When populating this table, you must use code to ensure there are no more than 3 rows for each project and that the empid is unique.

Proj1 EmpaID (empaID represents the employee id for employee a)
Proj1 EmpbID (empbID represents the employee id for employee b)
Proj1 EmpcID (empcID represents the employee id for employee c)

when you create the query, you will include all three tables. The new table tblEmpProj has all you need to get the project information and the employee information. Link the project id from tblempproj to the projectid in tblProjectData and also link the empid in tblEmpProj to the empid in tblEmployee. Now select the project, lastname and first name. If you still need help I can do a quick design in a database and include here.