Is this the best way to do this query?

[mhshane]

Hello, hope you are doing well.

It has been a while since I built a DB, so my skills are a bit rusty.

I am curious if there is a more elegant way to create this query.

Two Tables involved:
Contact_List has phone numbers
Headcount has manager FULL name

Both come from other sources on a monthly basis.

I want to be able to query an employee and see manager and manager phone number
Challenge is the Manager Full Name is all I have to go on for manager. No Manager Employee ID in the feed.

I created qryFullName to associate each employee ID to employee Full name
Col 1: [Headcount].[EmpID],
Col 2: FullName: [Headcount].[FirstName] & " " & [Headcount].[Lastname]

Then created qryMgrEmpID to associate each EmpID with their MgrEmpID
Headcount.Manager_Name joined to qryFullName.FullName



Finally, I created qryEmployee

qryFullName.EmpID	joined to	Contact_List.EmpID
qryFullName.EmpID	joined to	Headcount.EmpID
Headcount.EmpID	joined to	qryMgrEmpID.EmpID
qryMgrEmpID.EmpID	joined to	Contact_List_1.EmpID
qryMgrEmpID.EmpID	joined to	Headcount_1.EmpID

This serves my purpose so, minor kudos to brushing off dusty skills.

But I am curious if there is a better way to do this than creating two subqueries?

Thanks.

 

[Andrzejek]

Can you connect the 2 tables this way:

Headcount.ManagerName = Countact_List.LastName + " " + Countact_List.FirstName
or
Headcount.ManagerName = Countact_List.FirstName + " " + Countact_List.LastName