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);
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);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;
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;
ASKER
Thanks Pat
I guess you didn't need an updateable query.
ASKER
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.
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.
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.
ASKER
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.
You could but there is no need to do so. I just wanted you to understand why you shouldn't use Cartesian Products lightly.
ASKER
Thanks Pat. I appreciate your clarification.
ASKER