employee table

  • Thread starter Thread starter Souris
  • Start date Start date
S

Souris

I have an employee table which contains employee name and supervisors.
The supervisors link to employee table enployee number whick link to itself.

How should I do the relationship from Acess?

For example

TblEmployee

Employee Number Long Integer PK
First Name text(35)
Last Name text(35)
Supervisor Long Integer FK

The superviors links to employee table.

On the access how I can link the table itself.

Your information is great appreciated,
 
In the Relationships Window (Tools menu), add the Employee table twice.
Access aliases the 2nd one as Employee_1.

Drag Employee_1.Supervisor onto Employee.[Employee Number]

This kind of structure (self-join) is often useful. Here's another example:
http://allenbrowne.com/ser-06.html
 
Thanks for the message,

THis link looks like the supervisor links to all employees number.
Do I need do a filter for only supervisors in the table?
Does this link know that I have an employee for supervisors?

For example Employee 1 and employee 2 are employees. None of them is
supervior.

If I assign employee 1 be a supervisor of employee 2, does database check
the data integrity ?


Thanks again,



Allen Browne said:
In the Relationships Window (Tools menu), add the Employee table twice.
Access aliases the 2nd one as Employee_1.

Drag Employee_1.Supervisor onto Employee.[Employee Number]

This kind of structure (self-join) is often useful. Here's another example:
http://allenbrowne.com/ser-06.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Souris said:
I have an employee table which contains employee name and supervisors.
The supervisors link to employee table enployee number whick link to
itself.

How should I do the relationship from Acess?

For example

TblEmployee

Employee Number Long Integer PK
First Name text(35)
Last Name text(35)
Supervisor Long Integer FK

The superviors links to employee table.

On the access how I can link the table itself.

Your information is great appreciated,
Click to expand...
Click to expand...
 
The self-join only checks that the supervisor is a valid person. It does not
test whether the the status of that person.

Consequently, you can have a tiered structure where:
- anyone can be the supervisor of another,
- one supervisor can have someone else as their supervisor,
and so on.

If you do want to be able to flag some employees as supervisors, you could
add an IsSupervisor (yes/no) field. In your form, you could then set the
RowSource of this combo to a query where this field is True.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Souris said:
Thanks for the message,

THis link looks like the supervisor links to all employees number.
Do I need do a filter for only supervisors in the table?
Does this link know that I have an employee for supervisors?

For example Employee 1 and employee 2 are employees. None of them is
supervior.

If I assign employee 1 be a supervisor of employee 2, does database check
the data integrity ?


Thanks again,



Allen Browne said:
In the Relationships Window (Tools menu), add the Employee table twice.
Access aliases the 2nd one as Employee_1.

Drag Employee_1.Supervisor onto Employee.[Employee Number]

This kind of structure (self-join) is often useful. Here's another
example:
http://allenbrowne.com/ser-06.html

Souris said:
I have an employee table which contains employee name and supervisors.
The supervisors link to employee table enployee number whick link to
itself.

How should I do the relationship from Acess?

For example

TblEmployee

Employee Number Long Integer PK
First Name text(35)
Last Name text(35)
Supervisor Long Integer FK

The superviors links to employee table.

On the access how I can link the table itself.

Your information is great appreciated,
Click to expand...
Click to expand...
Click to expand...
 
THis link looks like the supervisor links to all employees number.
Do I need do a filter for only supervisors in the table?
Does this link know that I have an employee for supervisors?

For example Employee 1 and employee 2 are employees. None of them is
supervior.

If I assign employee 1 be a supervisor of employee 2, does database check
the data integrity ?
Click to expand...

The database will check that the supervisor is an employee... but that's all.

It won't require anyone to have a supervisor; it won't prevent loops such as
Joe being Bill's supervisor while Bill is also Joe's supervisor; it won't
prevent the new-hire part-time trainee from being made the supervisor of the
CEO. Some human interface will still be needed!

John W. Vinson [MVP]
 
Another way of modelling hierarchies like this is to use Joe Celko's 'nested
sets' model rather than the common 'adjacency list model', of which your
table is an example. One problem of the adjacency list model, which Joe has
pointed out and which you have spotted, is that it does not model
subordination. You'll find an article by Joe which explains his model in
more detail at:


http://www.intelligententerprise.com/001020/celko.jhtml


And you'll find it expalined at greater length in his book 'SQL for Smarties'.

Ken Sheridan
Stafford, England

Souris said:
Thanks for the message,

THis link looks like the supervisor links to all employees number.
Do I need do a filter for only supervisors in the table?
Does this link know that I have an employee for supervisors?

For example Employee 1 and employee 2 are employees. None of them is
supervior.

If I assign employee 1 be a supervisor of employee 2, does database check
the data integrity ?


Thanks again,



Allen Browne said:
In the Relationships Window (Tools menu), add the Employee table twice.
Access aliases the 2nd one as Employee_1.

Drag Employee_1.Supervisor onto Employee.[Employee Number]

This kind of structure (self-join) is often useful. Here's another example:
http://allenbrowne.com/ser-06.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Souris said:
I have an employee table which contains employee name and supervisors.
The supervisors link to employee table enployee number whick link to
itself.

How should I do the relationship from Acess?

For example

TblEmployee

Employee Number Long Integer PK
First Name text(35)
Last Name text(35)
Supervisor Long Integer FK

The superviors links to employee table.

On the access how I can link the table itself.

Your information is great appreciated,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top