Is this the best way to do this query? 1

[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

 

[Chriss Miller]

I agree, there is no point in first establishing the EmpID to join the Contact_List (with EmpId) and the Headcount (without EmpId) on the name. As a join condition can be on an expression, as Andy shows.

I just wonder how you'd ever know who is the manager of an employee, with or without knowing the EmpID of the manager. For that matter you have to have further data, don't you? If you have an employees table (I guess you have one explicitly about Employees, not Contact_List, who would call an employees table Contact_List?). If that employees table has a manager_ID that is an employee_D, then it makes sense to look up the EmpID from the manager name first, but not to make this join. You could also just have a manager flag in the emploeeys table and which employees a manager manages is indirectly determined by being part of the same department, for example.

However it is exactly, there's something missing to bring it all together correctly.

 

[Chriss Miller]

Both come from other sources on a monthly basis.

Not sure what this means? Varying third parties? Same sources each month? Is it only departments of your companies or third parties. And then, why would you get a list of manager names of your company from a third party? Again, anyway it is - I'd not tolerate such bad quality data, when it would be possible to get it with proper IDs, too. You shouldn't need to put extra and more complex effort in adding back a field that someone else just dropped off their lists. They acted unprofessionally and you don't have to mend that for them.

 

[NeoToad]

Thanks for sharing this, it's really helpful to see how you approached it. As someone just getting into working with queries again, I’m curious — if the manager name format ever changes slightly, wouldn’t that break the join? Would it be safer to push for getting a Manager ID added to the source files?

 

[Andrzejek]

If your data "come from other sources on a monthly basis" I would request:

PositionID
LastName
FirstName
EMail
Phone
...
ManagerPositionID

Where ManagerPositionID is a ForeignKey to PositionID PrimaryKey

Where I work, we also have this type of data kept by 'other sources' (third party software) and we can only get our own data thru a report from that place. Weird, but... what can you do?
But this structure allows you to know who is the Manager of your Manager, etc. (self-referencing table structure). Only the Director at the very top is his own Manager.

And I can keep the PositionID in my other table(s) so when people retire, move up, move between Departments, change the position, etc. the rest of my data is intact.

 

[Chriss Miller]

RE "Both come from other sources on a monthly basis." once more.

It would really be good to know more about what that means From own experience I can add that a company I worked for used a third party software for work time recording and they absolutely of course didn't use the same IDs for persons, people were signed in by mail address and name, obviously. But if "other sources" would be other departments only, within a company it should be possible to refer to the same things with the same data, i.e. and especially the same IDs.

Even if for some reson data about eployees and managers is exported to an Excel sheet that's without IDs for reasons of readability or the output sheets are processed within Excel in ways IDs would or could become muddled and the best you have is the name to reidentify someone: No, there are other ways llike fixed columns, column widths set to 0 and other means to transfer and keep IDs intact even when you mix and match with other software that internally uses other IDs, obviously.

You could also use the concept that's known as referral data. When it comes to data privacy protection ruling, you don't have to provide any personal data to third parties as long as placeholders that reidentify to your internal data can be used. It's a bit pointless for IDs, when they are just a sequential number generated by the database and don't include things like the social security number of a person, but you can always provide an ID that can then later be used to refer to the actual Manager/Employee, even if "other sources" actually mean third party or software working with their own, separate, data backend.

 

[mhshane]

Thanks for sharing this, it's really helpful to see how you approached it. As someone just getting into working with queries again, I’m curious — if the manager name format ever changes slightly, wouldn’t that break the join? Would it be safer to push for getting a Manager ID added to the source files?

It absolutely breaks if the person who makes the headcount spreadsheet misspells a name. Free text fields are always a bad field to work from, but it is the only one I have in this case.

The EmpID is my own. Headcount feed doesn't actually contain EmpID and I don't want to ask the author to change her spreadsheet. I added a number to each employee as a best practice in DB design.

I've debated on making the MgrEmpID a static field in the table and when new people are added I will just have to do the adds manually. Probably will just to keep it clean.

 

[mhshane]

RE "Both come from other sources on a monthly basis." once more.

It would really be good to know more about what that means From own experience I can add that a company I worked for used a third party software for work time recording and they absolutely of course didn't use the same IDs for persons, people were signed in by mail address and name, obviously. But if "other sources" would be other departments only, within a company it should be possible to refer to the same things with the same data, i.e. and especially the same IDs.

Even if for some reson data about eployees and managers is exported to an Excel sheet that's without IDs for reasons of readability or the output sheets are processed within Excel in ways IDs would or could become muddled and the best you have is the name to reidentify someone: No, there are other ways llike fixed columns, column widths set to 0 and other means to transfer and keep IDs intact even when you mix and match with other software that internally uses other IDs, obviously.

You could also use the concept that's known as referral data. When it comes to data privacy protection ruling, you don't have to provide any personal data to third parties as long as placeholders that reidentify to your internal data can be used. It's a bit pointless for IDs, when they are just a sequential number generated by the database and don't include things like the social security number of a person, but you can always provide an ID that can then later be used to refer to the actual Manager/Employee, even if "other sources" actually mean third party or software working with their own, separate, data backend.

Headcount and Contact_List are spreadsheets created by other people in HR.
3rd party just means, I am not the author and don't have control over the data that is published.
Sorry for the confusion.

In my situation, manager name is what I have to work with to ID manager. Not ideal at all being a free text field.
EmpID in my DB is my own numbering so I am not working with text fields.
Probably be best for me to add a static column to my Emp table for ManagerEmpID and eliminate the need to rely on the Manager Name field. If HR misspells a name, the query for that user will fail.
I am thinking it would be better to just do an update to the table once to add MgrEmpID and then add new users when needed.

 

[mhshane]

I streamlined the design down to one subquery (qryEmpFullName) and my main query (qryEmpInfo)
And I relearned how to use table aliases

qryEmpFullName
SELECT
EmpID,
FirstName,
LastName,
PhoneNumber,
FirstName & " " & LastName AS FullName
FROM
Contact_List;


qryEmpInfo
SELECT
Headcount.FirstName & " " & Headcount.LastName AS [Employee Full Name],
CL.PhoneNumber AS [Employee Phone],
Headcount.Manager_Name AS [Manager Full Name],
M.PhoneNumber AS [Manager Phone]
FROM
(Headcount
INNER JOIN Contact_List AS CL ON Headcount.EmpID = CL.EmpID)
LEFT JOIN Contact_List_WithFullName AS M
ON Headcount.Manager_Name = M.FullName;


I did also find I can do the whole thing in one query by joining to the calculated expression
LEFT JOIN Contact_List_WithFullName
ON Headcount.Manager_Name = Contact_List_WithFullName.FullName

But that can only be done in SQL View.
Design View will not display joins on calculated expressions.
I was being lazy and trying to avoid having to relearn SQL, but I repent now and brushing up on my SQL.

Overall this was a good refresher for me.
I appreciate your input.