Self referencing within same table...

[Guest]

Hello.

I have a table with Employee data, to inlcude the name of the employee's
supervisor. I wish to self reference the supervisor to the Employee ID,
within the same table. I have seen illustrations that this can be done, but
no explanation as to how. Reason that I like this approach is that I would
like to make a change only once to the Supervisor name (text) and have this
name cascade to all instances of the name for the given employees. I don't
know if I'm explaining this properly, but the solution has something to do
with draging a copy of the employee table down twice in he Relationship
window and somehow making the relation there.

I don't wish to make a seperate table containing supervisors.

Any ideans?

Thank you.

NWO

 

[Duane Hookom]

Your supervisor field in your employee table would be the same data type as
EmployeeID (if autonumber change to numeric-long).
You can then create a join between tblEmployees.EmployeeID and
tblEmployees_1.SupervisorID.

 

[Guest]

Thank you Duane.

NWO
-----------

Your supervisor field in your employee table would be the same data type as
EmployeeID (if autonumber change to numeric-long).
You can then create a join between tblEmployees.EmployeeID and
tblEmployees_1.SupervisorID.

 

[Pat Hartman\(MVP\)]

I'll add a little more detail.
To actually create the query, you need to add the employee table to the QBE
grid twice. The second table instance will have a _1 appended to its name
by Access so the two can be distinguished. Draw the join line from the
SupervisorID in the first instance of the table to the EmployeeID in the
second instance. In order to avoid confusion with names since you will be
selecting columns from both tables, you need to alias the supervisor fields.
To do that, insert a new name in front of the FirstName and LastName and
separate them with a colon.

SupervisorLastName:LastName
SupervisorFirstName:FirstName
etc. for any other fields selected from the second table instance.

 

[Guest]

Hi NWO

This requires a type of query called a Self Join. You need to have 2
instances of the Employees table and at least 1 instance (preferably both)
will require an alias. You can do it in the Access QBE window by adding 2
instances of the Employees table and then selecting fields from each. There
is a more detailed explanation at:-

http://www.databasedev.co.uk/self-join_query.html

The SQL code would be something like:-

SELECT E1.EmployeeID, E1.LastName, E1.FirstName, E2.EmployeeID, E2.LastName,
E2.FirstName
FROM Employees AS E1
LEFT JOIN Employees AS E2
ON E1.SupervisorID = E2.EmployeeID;

The LEFT JOIN will include anyone who doesn't have a supervisor, e.g. the
boss! An INNER JOIN would include only those employees who do have a
supervisor.

Hope this helps!

Andrew

 

[Guest]

Thank you all for the valuable feedback.

NWO
--------------

 

[Guest]

Andrew, regarding the ...co.uk/... link, could one then use an update query
to assign new supervisor name for a given set of employees - thanx.

NWO
--------------

 

[Guest]

Hi NWO

Yes, you can use an Update query something like:-

UPDATE Employees
SET SupervisorID = 3
WHERE SupervisorID = 2

This will change the SupervisorID to 3 for all employees whose current
Supervisor has an ID of 2.

You could use parameters in your query to enable the Employee numbers to be
entered as required - something like:-

UPDATE Employees
SET SupervisorID = NewID
WHERE SupervisorID = OldID

You will then be asked to input the NewID followed by the OldID.

If you want to specify names rather than numbers it all gets rather more
complicated.

Have Fun!

Andrew

 

[Guest]

Thank you again Andrew.

Merry Christmas and Happy New Years

NWO
-------------------