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.
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.
Last edited:
