recrusive Manager-Employee Table?

[goeb86]

I usually have difficulty explaining my issues on access but I do need some some assistance/expertise from this glorious forum in order to make my life easier at work, so here it goes:

Basically, I started a job a few months ago and there is a spreadsheet (in Excel) with (firstly) 6 columns of basic employee information (basic attributes such as ID#, name, e-mail, etc) and then subsequent to those columns are columns with their respective manager (manager name, e-mail)

Now I already cleaned it up a bit by importing the table into Access and created a new field for the Manager's ID (which is their respective employee ID ggrr!). Now here is what I would like to do....

Every employee in the company (from CEO to Custodian) has an employee ID, all employees are also listed in the same table....so I figured, rather than do a vlookup in excel to auto-populate the manager e-mail and name (once I enter in their Manager ID), maybe I could somehow do a recursive relationship so it auto-populates those fields. I don't think creating a separate manager table would be a good idea since every manager is considered an employee in this company, and and as a result they already have an ID (I would imagine giving them their own Manager table with a new ID would just complicate things and duplicate data which wouldn't make the higher-ups very happy). The Manager-Employee relationship is a one to Many relationship.

I attempted to do a recursive relationship using the Employee ID as a primary key and using the Manager ID as a foreign key (outer join), but it did not work at all. I can attempt to post a visual if that would make things easier. Right now though, my only options are to do vlookups in Excel and generate an imported linked table in Access, or do a self-join query (which does work well, but very inconvenient to do each time).

Thanks to anyone who has read this post. I do apologize if it is convoluted, as I have only been dealing with Access for 6 months and am self-taught (Unfortunately I know the most about Access in my department, so I have no one else to turn to). I welcome any suggestions.

 

[goeb86]

Depending on exactly what you want to display, you probably do not need a subform. You could put a listbox or a combobox on a form for the Manager Name and when selected it would show all employees he manages. Remember that building the one to many Relationship in the Relationship window is key to get the manager table to display the built-in subsheet.

Happy to help!

igold

For now, it would just be the Manager's name and e-mail ....Down the line I may add a few more fields if the other users of the database seem content with it.

 

[goeb86]

An extra table does nothing to help here. Normally you'd just have a manager field on the employee table that is a foreign key to the employee Id. You simply then join the table to itself to get the manager/employees.

If you want to display the full org hierarchy, this is more difficult and you're roght, you'd need recursion, in sql server you'd use a common table expression for this, but I don't believe that they're supported in access sql

Does it have to be self-joined with a query each time though?...when I attempted to do a recursive/self join relationship in the relationship table, the subdatasheet was empty (I tried modifying the relationship type and link but couldn't seem to get it to work properly).

It only seems to work through a self-join query.

 

[Kyle123]

There's nothing recursive here so I don't follow your terminology.

Your last statement seems to contradict your first paragraph

You also wouldn't have a manager email field, a manager is just an employee so you read it from the employeeemail

 

[goeb86]

There's nothing recursive here so I don't follow your terminology.

Your last statement seems to contradict your first paragraph

You also wouldn't have a manager email field, a manager is just an employee so you read it from the employeeemail

Well isn't a recursive table when a table creates a relationship with itself? When I enter in the Manager ID (which to clarify, is the employee ID of that manager), how do I configure Access to retrieve the employee e-mail and name for that manager (without using a query each time)?

That is really my issue.

 

[Darren Bartrup]

With one employee table with these fields:
Employee_ID (PK)
Employee_Fname
Employee_Lname
Manager_ID (FK)

The MD of the company won't have a manager so his Manager_ID field is left NULL.

This query will return the employee name and the manager name:

SELECT  A.Employee_Fname & " " & A.Employee_Lname AS EmployeeName,
        A.Employee_Email,
        B.Employee_Fname & " " & B.Employee_Lname AS ManagerName
FROM    tbl_Employees A LEFT JOIN tbl_Employees B ON B.Employee_ID = A.Manager_ID

I think that's what you're after. <style type="text/css">span { font-family: 'Courier New'; font-size: 10pt; color: #000000;}.sc0 {}.sc5 { font-weight: bold; color: #0000FF;}.sc6 { color: #808080;}.sc10 { font-weight: bold; color: #000080;}.sc11 {}</style>

 

[goeb86]

With one employee table with these fields:
Employee_ID (PK)
Employee_Fname
Employee_Lname
Manager_ID (FK)

The MD of the company won't have a manager so his Manager_ID field is left NULL.

This query will return the employee name and the manager name:

SELECT  A.Employee_Fname & " " & A.Employee_Lname AS EmployeeName,
        A.Employee_Email,
        B.Employee_Fname & " " & B.Employee_Lname AS ManagerName
FROM    tbl_Employees A LEFT JOIN tbl_Employees B ON B.Employee_ID = A.Manager_ID

I think that's what you're after. <style type="text/css">span { font-family: 'Courier New'; font-size: 10pt; color: #000000;}.sc0 {}.sc5 { font-weight: bold; color: #0000FF;}.sc6 { color: #808080;}.sc10 { font-weight: bold; color: #000080;}.sc11 {}</style>

Thanks Darren...but my wquesiton was if I could perform that same aciton without running a query (I have created that same query before, but I would rather not have to run it for each managerial update I receive)...just by creating a relationship between the employee table & itself (i.e. Employeetable_1) so it would just create a subdatasheet next to the employee name and show the employee's respective manager's info.

Once again, I apologize if I have not been clear about that. I am trying to improve my articulation skills when it comes to explaining my end goals through text. I appreciate you generating that code for me though, very kind of you.