Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

How to retrieve employee id of managers

Hello,
I'm working on an application that I inherited.  There is an employee table that lists all the employees as well as the managers.  The problem is that the table uses a the last name of the manager as a key field rather than a manager id field.  I'm added a mgr_id field.  I am now trying to populate the mgr_id field by executing a query that returns the employee id of each manager and using that employee id field named CUID to populate the mgr_id field that I added.  I started off by writing a sql query that returns that information to verify that I was selecting the correct value.  However, it is still retreiving only the cuid of the employee and not of the manager.

SELECT Employee.CUID, Employee.Mgr_LName
FROM Employee
WHERE Employee.[Mgr_LName] In (SELECT LName FROM Employee);
0
chtullu135
Asked:
chtullu135
  • 5
  • 4
1 Solution
 
ReneD100Commented:
SELECT E.CUID, E.Mgr_LName, M.CUID
FROM Employee E, Employee M
WHERE E.Mgr_LName=M.LName

Open in new window

The E.CUID is the Employee ID, the M.CUID is the Managers' CUID
0
 
chtullu135Author Commented:
Thanks Rene.  I was starting to write a recursive query and you saved me quite a bit of time.
0
 
PatHartmanCommented:
You need to specify a join type or you will end up with a non-updatable query.

SELECT E.CUID, E.Mgr_LName, M.CUID
FROM Employee E Inner Join Employee M On E.Mgr_LName=M.LName

Of course, this update will be problematic if you have multiple employees/managers with the same last name so I would check that first by creating a duplicates query to look for employee records that duplicate last names.

Select LName, Count(*) As RecCount
From Employee
Group By LName
Having Count(*) > 1;
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
chtullu135Author Commented:
Thanks Pat
0
 
PatHartmanCommented:
I guess you didn't need an updateable query.
0
 
chtullu135Author Commented:
Hello Pat,

In this case, the solution posted by Rene works.  However, I'm using your solution for another problem.  To be honest, I much prefer the join syntax as it more clearly delineates the where condition.
0
 
PatHartmanCommented:
Access treats the two queries very differently.  Rene's query produces a Cartesian Product where every row from table 1 is matched against every row from table 2 and the recordset is then whittled down by the criteria.  That is why the query is not updateable.  you can also run into memory issues if the two tables have more than a small number of rows.  1,000 rows x 10,000 rows = 10 million rows!!! before the criteria is applied.  So, unless you really want a Cartesian Product, I would never use Rene's query.  Since every row is joined to every other row, no indexes or other shortcuts can be employed by the query engine.
0
 
chtullu135Author Commented:
In my case, the solution posted by renee works since I didn't need an updatable query.  But I do see your point and will use that approach.  However since Renee first, provided me with the solution that worked for the problem I was working I gave Renee the points, before I saw your answer.  I'm not sure if I can reasign points at this stage.
0
 
PatHartmanCommented:
You could but there is no need to do so.  I just wanted you to understand why you shouldn't use Cartesian Products lightly.
0
 
chtullu135Author Commented:
Thanks Pat.  I appreciate your clarification.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now