How do I join multiple fields in one table to a single field on another table?
Posted on 2011-03-20
I know the solution to this is going to be embarrassingly easy, but my brain seems to be stuck. I have two tables, one called "Projects" and one called "Employees". There are several employees associated with each project so I have fields in the project table called "Owner", "Originator", "Customer", etc. where only the employee ID is stored in the "Projects" table. In the "Employees" table I of course have the fields for each employee, "EmployeeID", "LastName", "FirstName". When I do the query to pull up a list of projects, I want to be able to show the owner's first and last name, the originator's first and last name, the customer's first and last name, etc.
If it was just the owner, the query would look like this:
SELECT tblProjects.ProjectName, [tblEmployees]![FirstName]+" "+[tblEmployees]![LastName] AS Owner
FROM tblEmployees INNER JOIN tblProjects ON tblEmployees.ID = tblProjects.Owner;
I can't figure out how to display the originator and customer similarly.
Also, if anyone who answers this would tell me how I could have asked this question better I would appreciate it.