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.

 

[igold]

Hi goeb86,

I have used Access for many years and I am sure I could help you, but I lost you somewhere mid-post. You have one large employee table that lists the entire company. The first six columns is the personal stuff and the next few columns to the right are their manager email/name. This is where I lost you, what do want to do exactly. Is it that you want to be able create query/form and be able to show all employees for a certain manager?

Your scenario is pretty straightforward, I am sure I could help you if you tell me what you are trying to accomplish...

igold

 

[goeb86]

Hi igold,

I will try to explain it a bit more coherently:

1) Lets say hypothetically that there is a table with a list of employees and their respective managers:

Employee_ID, Employee_Fname, Employee_LName, Employee_Email, the respective Manger's Employee ID# (Manager_ID), Manager_FName, Manager_Lname, Manager_email

Here is a visual to help explain

Employee_ID Employee_Fname Employee_Lname Employee_Email Manager_ID Manager_Fname Manager_Lname Manager_Email 1 Jack Smith jack.smith@something.com 4 Jason Black Jason.Black@something.com 2 David Dodson david.dodson@something.com 5 Mike Green Mike.Green@something.com 3 Rob Blue Rob.Blue@something.com 4 Jason Black Jason.Black@something.com 4 Jason Black Jason.Black@something.com 5 Mike Green Mike.Green@something.com 5 Mike Green Mike.Green@something.com 5 Mike Green Mike.Green@something.com <colgroup><col><col><col><col><col><col><col><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[goeb86]

Sorry didn't know how to escape the table and go back to the post body after the table, so this reply will be in two posts.

So jsut to clarify.....I currently use a Vlookup (self-lookup) in Excel to fill the Manager fields which is triggered by the Manager's employee ID, i.e. Jack Smith's manager has an employee ID value of 4, so the vlookup looks up 4 in the employee fields and pulls the first name, last name, etc of the Manager and it self-populates.

So I wonder if I could do this in Access with a recursive relationship, so the manager fields will turn into a subdatasheet after filling in the Manager ID. If this can be done with a form, which automatically generates the employee's manager's name and e-mail just with the input of a Manager ID, that would be fine too. I just want to get the manager's info auto-populated one way or another. I just don't think creating a separate Manager's table is the right solution as they alreayd have employee ID's, giving them Manager ID's on top of that might lead to poor data structure (although I am fully aware the current table I am using is far form perfect, but it is the hand I have been dealt with unfortunately for now)

 

[igold]

Your are trying to be way to complicated. Try to get away from using the Access jargon and just tell me want you want to do. If you are trying to do a work around for creating a separate table for the Managers, my first inclination is that you should create the second table. It is not question of making things more complicated it is a question what best represents a one to many relationship which is table to table. That is what Access does. Create the table, set up the relationship in the relationship window, open the Manager table and boom you have your sub-sheet built in. Don't create a ID for the managers use their current, as long as it is unique and you can tie the ID to a field in the employee table you're good.

 

[goeb86]

Your are trying to be way to complicated. Try to get away from using the Access jargon and just tell me want you want to do. If you are trying to do a work around for creating a separate table for the Managers, my first inclination is that you should create the second table. It is not question of making things more complicated it is a question what best represents a one to many relationship which is table to table. That is what Access does. Create the table, set up the relationship in the relationship window, open the Manager table and boom you have your sub-sheet built in. Don't create a ID for the managers use their current, as long as it is unique and you can tie the ID to a field in the employee table you're good.

Thanks!

 

[igold]

Did I help or was that a polite way of saying I will find another answer?

 

[goeb86]

I am going to try it out on Monday (or Tuesday) to be honest. I will update you to see if it works. If it does, then I will probably then try to create it in form view (which I guess involves a subform, I don't think it is too complicated) so the rest of my team can have everything neatly laid out in a user-friendly format.

Sorry, should've been more clear.

 

[igold]

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

 

[Kyle123]

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