Query trouble - two tables, should be simple...

[storm75m]

I don't know why this is giving me so much trouble, seems pretty straight forward. I have two tables:

tblEmployees
EmpID
EmpName

tblArea
AreaID
AreaName
PrimaryContactID (fk to Employees table)
SecondaryContactID (fk to Employees table)

I just need a simple query to show the data like this:

AreaID
AreaName
PrimaryContactName
SecondaryContactName

Do I need to do some kind of Dlookup function or something? A sample would be WONDERFUL, thanks!

 

[lespaul]

SELECT AreaID, AreaName, e1.EmpName, e2.EmpName
FROM tblArea
INNER JOIN tblEmployees e1 on tblArea.PrimaryContactId = E1.EmpID
INNER JOIN tblEmployees e2 on tblArea.SecondaryContactID = e2.EmpID

Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell

 

[PHV]

If the ContactID FKs are optional:
SELECT AreaID, AreaName, P.EmpName AS PrimaryContactName, S.EmpName AS SecondaryContactName
FROM (tblArea AS A LEFT JOIN tblEmployees AS P ON A.PrimaryContactId = P.EmpID)
LEFT JOIN tblEmployees AS S ON A.SecondaryContactID = S.EmpID ;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[storm75m]

Worked great, thanks. Never had to do multiple joins to the same table before.