Repost: Connecting a table to itself?

[davegb]

I posted this message earlier, got a reply to google for a previous
reply, which I couldn't find, so here I am again.

I've run across another situation in Access I just don't know how to
handle. I have a database of employees in our group. I have a series on

linked tables with location, cubicle, computer, etc. All that is fine
(well, since I got help here with it, it's fine!). I want to show who
each person's supervisor is. I could put in a separate list of
supervisors, most of whom would also be in the employees list. But
doesn't it make more sense to put in a boolean field in the employee
table for supervisor? Then extract a separate table for just
supervisors and relevant information? And link that table back to the
employee table? But then what happens when a new supervisor is named?
The supervisor table would have to be deleted, along with the link, for

the query to re-create the supervisor table. Somehow, what doesn't
sound right to me.
The other problem it creates is the the head of the department's
supervisor is not an employee of the department. So I'd either have to
leave the dpt. supervisor with no supervisor, or add the dpt's
supervisor's supervisor as an employee, which isn't really true. Is
this confusing you as much as it is me?
I'm sure many of you have resolved these difficulties eons ago. So what

are the best solution(s) for this situation?

[pietlinden]

I want to show who each person's supervisor is.
table structure is something like this...

CREATE TABLE employee (
EmployeeID LONG NOT NULL,
FirstName TEXT(20) NOT NULL,
LastName TEXT(20) NOT NULL,
....
SupervisorID LONG,

FOREIGN KEY (SupervisorID) REFERENCES employee(employeeID));

in your form, you can make SupervisorID a combobox - you know the drill
- show employee.FirstName & " " & employee.LastName.

The only catch is that you'll have to either start with the data from
the top of the hierarchy (most senior staff first) and work your way
down to the least senior employees.

then in the OnCurrent event of your form, you may have to requery the
supervisor combobox.

Hope this gets you started

[pietlinden]

I want to show who each person's supervisor is.
table structure is something like this...

CREATE TABLE employee (
EmployeeID LONG NOT NULL,
FirstName TEXT(20) NOT NULL,
LastName TEXT(20) NOT NULL,
....
SupervisorID LONG,

FOREIGN KEY (SupervisorID) REFERENCES employee(employeeID));

in your form, you can make SupervisorID a combobox - you know the drill
- show employee.FirstName & " " & employee.LastName.

The only catch is that you'll have to either start with the data from
the top of the hierarchy (most senior staff first) and work your way
down to the least senior employees.

then in the OnCurrent event of your form, you may have to requery the
supervisor combobox.

Hope this gets you started

[davegb]

pi********@hotmail.com wrote:

I want to show who each person's supervisor is.
table structure is something like this...

CREATE TABLE employee (
EmployeeID LONG NOT NULL,
FirstName TEXT(20) NOT NULL,
LastName TEXT(20) NOT NULL,
...
SupervisorID LONG,

FOREIGN KEY (SupervisorID) REFERENCES employee(employeeID));

in your form, you can make SupervisorID a combobox - you know the drill
- show employee.FirstName & " " & employee.LastName.

The only catch is that you'll have to either start with the data from
the top of the hierarchy (most senior staff first) and work your way
down to the least senior employees.
then in the OnCurrent event of your form, you may have to requery the
supervisor combobox.

Hope this gets you started

pietlin, thanks for your reply. I would have liked to reply sooner, but
this particular project is not my highest priority.
I should have stated that I am a newbie to Access and not at all
familiar with how it works, so, no, I don't "know the drill". My
questions are very basic. I did do some research in Prague's book and
in this NG before posting my reply. You show some of the fields as "Not
Null". I looked for this a number of places, but only found anything in
reference to filters and VBA, not tables. I'm guessing that this would
be the same as "Required" in the row characteristics. Is that correct?
I couldn't find any other fields where "Not null" seemed to apply.

You also had:
SupervisorID LONG,

I'm guessing this is another field in the separate table(Supervisor
Table) from the Employee table and would be a key. Is that correct?

And then:
FOREIGN KEY (SupervisorID) REFERENCES employee(employeeID))
Does this mean that Supervisor ID is a field in the employee table and
is linked 1:many with the Supervisor table?

Thanks for your help.

[pietlinden]

Okay, I'll try it in English this time =) I'm assuming the simplest
solution to this problem.

Essentially what you have is Employee (in supervisor role) supervises
another employee (who may or may not be a supervisor. If you diagram
this (a good thing to learn how to do, because it makes thinking
through your design a lot easier), you have something like this:
[Employee]----(1,M)----<supervises>---(1,1)---[Employee]
(aka "Supervisor" or "Manager")

In English:
A supervisor or manager may supervise one or more Employees.
Each employee has a supervisor.

Okay, so essentially what we have is a self-join. Employee supervises
another Employee. So if each employee has only one supervisor, we can
model this by keeping the "supervises" fact in the Employee table. So
we just put the "SupervisorID" in the Employee table, and then we can
join the table to itself in a query (by adding another copy and
aliasing it.) Something like...

qryShowEmployeeAndSupervisor

SELECT Supervisor.EmployeeID, Supervisor.FirstName,
Supervisor.lastName, tblEmployee.EmployeeID, tblEmployee.FirstName,
tblEmployee.lastName
FROM tblEmployee AS Supervisor RIGHT JOIN tblEmployee ON
Supervisor.EmployeeID = tblEmployee.SupervisorID;

Right Join is so that I can see all employees, whether or not they have
a supervisor.

Nope. SupervisorID is a field in the employee table. You're just
adding the same table to the query twice and then renaming one
"manager" and leaving the other "employee". then since all managers
are employees, we can just store the employee ID of the managing
employee in the ManagerID field. And we're sorted.

[Mondrogan]

It seems like it would be much easier *not* to assume that your employees
belong to one department (yours). If you go this route, your Employees table
must include a Department ID field for each employee (and you probably also
want a Departments table). Granted, it seems as if this information is
redundant in every case except for the supervisor of your department head
(who actually is not a member of your department) but I think that that
exception justifies the design. Now your Employees table can be keyed by
Employee ID and you can include a Supervisor ID field (which of course would
be someone's Employee ID). After this it seems that your other problems
disappear. Or am I missing something?

Btw, you can limit your Departments table to two departments: yours, and the
department of your department head's supervisor.

"davegb" <da****@safebrowse.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

I posted this message earlier, got a reply to google for a previous
reply, which I couldn't find, so here I am again.

I've run across another situation in Access I just don't know how to
handle. I have a database of employees in our group. I have a series on

linked tables with location, cubicle, computer, etc. All that is fine
(well, since I got help here with it, it's fine!). I want to show who
each person's supervisor is. I could put in a separate list of
supervisors, most of whom would also be in the employees list. But
doesn't it make more sense to put in a boolean field in the employee
table for supervisor? Then extract a separate table for just
supervisors and relevant information? And link that table back to the
employee table? But then what happens when a new supervisor is named?
The supervisor table would have to be deleted, along with the link, for

the query to re-create the supervisor table. Somehow, what doesn't
sound right to me.
The other problem it creates is the the head of the department's
supervisor is not an employee of the department. So I'd either have to
leave the dpt. supervisor with no supervisor, or add the dpt's
supervisor's supervisor as an employee, which isn't really true. Is
this confusing you as much as it is me?
I'm sure many of you have resolved these difficulties eons ago. So what

are the best solution(s) for this situation?

[davegb]

pi********@hotmail.com wrote:

Okay, I'll try it in English this time =) I'm assuming the simplest
solution to this problem.

Essentially what you have is Employee (in supervisor role) supervises
another employee (who may or may not be a supervisor. If you diagram
this (a good thing to learn how to do, because it makes thinking
through your design a lot easier), you have something like this:
[Employee]----(1,M)----<supervises>---(1,1)---[Employee]
(aka "Supervisor" or "Manager")

In English:
A supervisor or manager may supervise one or more Employees.
Each employee has a supervisor.

Okay, so essentially what we have is a self-join. Employee supervises
another Employee. So if each employee has only one supervisor, we can
model this by keeping the "supervises" fact in the Employee table. So
we just put the "SupervisorID" in the Employee table, and then we can
join the table to itself in a query (by adding another copy and
aliasing it.) Something like...

qryShowEmployeeAndSupervisor

SELECT Supervisor.EmployeeID, Supervisor.FirstName,
Supervisor.lastName, tblEmployee.EmployeeID, tblEmployee.FirstName,
tblEmployee.lastName
FROM tblEmployee AS Supervisor RIGHT JOIN tblEmployee ON
Supervisor.EmployeeID = tblEmployee.SupervisorID;

Right Join is so that I can see all employees, whether or not they have
a supervisor.

Nope. SupervisorID is a field in the employee table. You're just
adding the same table to the query twice and then renaming one
"manager" and leaving the other "employee". then since all managers
are employees, we can just store the employee ID of the managing
employee in the ManagerID field. And we're sorted.

Pietlin,
Thanks for the reply and the simplification. It's starting to make
sense, a little at a time.
I added a new number field to the Employee table named SupervisorID. I
then created a query using 2 Employee tables with a join between them,
EmployeeID to SupervisorID (right join is option 2, correct?). So far
so good.
So now do I just copy the Supervisor's EmpID and paste it into the
SupervisorID field to make the assigments? That only allows me to show
one employee per supervisor. How do I show all the employees under a
given supervisor to show?

[davegb]

davegb wrote:

pi********@hotmail.com wrote:

Okay, I'll try it in English this time =) I'm assuming the simplest
solution to this problem.

Essentially what you have is Employee (in supervisor role) supervises
another employee (who may or may not be a supervisor. If you diagram
this (a good thing to learn how to do, because it makes thinking
through your design a lot easier), you have something like this:
[Employee]----(1,M)----<supervises>---(1,1)---[Employee]
(aka "Supervisor" or "Manager")

In English:
A supervisor or manager may supervise one or more Employees.
Each employee has a supervisor.

Okay, so essentially what we have is a self-join. Employee supervises
another Employee. So if each employee has only one supervisor, we can
model this by keeping the "supervises" fact in the Employee table. So
we just put the "SupervisorID" in the Employee table, and then we can
join the table to itself in a query (by adding another copy and
aliasing it.) Something like...

qryShowEmployeeAndSupervisor

SELECT Supervisor.EmployeeID, Supervisor.FirstName,
Supervisor.lastName, tblEmployee.EmployeeID, tblEmployee.FirstName,
tblEmployee.lastName
FROM tblEmployee AS Supervisor RIGHT JOIN tblEmployee ON
Supervisor.EmployeeID = tblEmployee.SupervisorID;

Right Join is so that I can see all employees, whether or not they have
a supervisor.

Nope. SupervisorID is a field in the employee table. You're just
adding the same table to the query twice and then renaming one
"manager" and leaving the other "employee". then since all managers
are employees, we can just store the employee ID of the managing
employee in the ManagerID field. And we're sorted.

Pietlin,
Thanks for the reply and the simplification. It's starting to make
sense, a little at a time.
I added a new number field to the Employee table named SupervisorID. I
then created a query using 2 Employee tables with a join between them,
EmployeeID to SupervisorID (right join is option 2, correct?). So far
so good.
So now do I just copy the Supervisor's EmpID and paste it into the
SupervisorID field to make the assigments? That only allows me to show
one employee per supervisor. How do I show all the employees under a
given supervisor to show?

I redid all this and reversed the tables so the employee info was in
the columns to the left and the supervisor was to the right. At least
that way I can see each employee's supervisor, although the table is
clunky and hard to interpret at best. Any ideas on how to make it more
readable?