自我加入。员工和主管查询。 [英] Self join. Employees and supervisors query.

问题描述

我对这个标准的员工 - 主管情况有疑问


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


我想向所有员工展示归属感。对于特定的主管。


例如，EMP1和EMP2都有相同的主管SUP1。 EMP3有一名主管

SUP2。 EMP4有主管SUP3。


SUP1和SUP2反过来拥有相同的老板SUP4。


我现在想创建一个表格，有两个listboxes，lb1和lb2。

在lb1中我向管理员展示，在lb2中，我向所有员工展示。

然后我选择SUP1表格lb1。 lb2现在只显示EMP1和EMP2，因为它们属于
属于到SUP1。


如果我在lb1中选择SUP4，我希望lb2显示EMP1，EMP2，SUP1和SUP2，因为

它们都是SUP4''下属。


奖金问题：


我希望能够在
$中使用未知数量的级别b $ b员工 - 主管结构。

所以我想用...创建一个表单......让我们说5个列表框（我可以想象

out out how to我自己动态创建......）

然后是一个显示员工的列表框。

第一个列表框，应该显示顶级主管（那些做的没有

有一个主管）。

第二个列表框应该显示所有二级主管，即那些有主管的b
但不是那些主管有主管的人。

依旧......

首次打开表格时，所有员工都会显示在列表框中。

如果您在任何列表框中选择主管，则会缩短列表。

如果您选择其中一个上级主管，则其他组合框

和列表框应该缩短。


任何和所有帮助表示赞赏，


/ jim

I have a problem with this standard employee-supervisor scenario

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

I want to show all employees "belonging" to a specific supervisor.

E.g, EMP1 and EMP2 both has the same supervisor SUP1. EMP3 has a supervisor
SUP2. EMP4 has supervisor SUP3.

SUP1 and SUP2 in turn have the same boss SUP4.

I now want to create a form, with two listboxes, lb1 and lb2.
In lb1 I show the supervisors and in lb2, I show ALL employees.
I then select SUP1 form lb1. lb2 now only shows EMP1 and EMP2 since they
"belong" to SUP1.

If I select SUP4 in lb1, I want lb2 to show EMP1, EMP2, SUP1 and SUP2, since
they are all SUP4''s underlings.

Bonus question:

I want to be able to work with an unknown number of levels in the
employee-supervisor structure.
So I want to create a form with ... hm... lets say 5 listboxes (I can figure
out how to create those dynamically on my own...)
And then a listbox that shows the employees.
The first listbox, should show the top-level supervisor (those that do not
have a supervisor).
The second listbox should show all second-level supervisors, that is, those
who have a supervisor, but not those whose supervisor has a supervisor.
And so on...
When you first open the form, all employees are shown in the listbox.
If you select a supervisor in any of the listboxes, the list is shortened.
If you select one of the upper-level supervisors both the other comboboxes
and the listbox should be shortened.

Any and all help appreciated,

/jim

推荐答案

不确定
主管（即那些主管只有一名主管的人在特定的方框中显示了b $ b。），但是，你可以通过设置第一个列表框来显示

每个没有主管的人框中：

SELECT employeeID，来自qryEmployeesPlusSupervisors的名称

isnull（supervisorID）

然后你可以设置第二个列表框的行源to：

SELECT employeeID，来自qryEmployeesPlusSupervisors的名称，其中supervisorID =

形成！yourform.lstBox1

并重新查询afterupdate事件中的第二个列表框第一个

列表框。

然后你会对其余的列表框做同样的事情，每个

参考前一个用于其行源的标准。

然后当你从上到下工作时，每个列表框都会显示

由被选中人员监督的人上一个方框。


希望这有帮助

-John

Jim Andersen < JB **** @ politi.dk>在消息中写道

news：43 ********************* @ dread11.news.tele.dk。 ..

Not sure about the initial showing of all the different levels of
supervisors (i.e. people whose supervisors have only one supervisor are
shown in a particular box.), but, you could have the first listbox show
everyone with no supervisor by setting the rowsource of the box to:
SELECT employeeID, Name from qryEmployeesPlusSupervisors where
isnull(supervisorID)
then you could set the second listbox''s rowsource to:
SELECT employeeID, Name from qryEmployeesPlusSupervisors where supervisorID=
forms!yourform.lstBox1
and requery the second list box in the afterupdate event of the first
listbox.
Then you''d do the same thing for the rest of the listboxes, having each
refer to the previous one for the criterion of its rowsource.
Then as you worked your way from the top down, each list box would show
people who are supervised by the person selected in the previous box.

hope this helps
-John

"Jim Andersen" <jb****@politi.dk> wrote in message
news:43*********************@dread11.news.tele.dk. ..

我对这个标准的员工 - 主管情况有疑问

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

我想要向所有员工展示归属感对于特定的主管。

例如，EMP1和EMP2都有相同的主管SUP1。 EMP3有一个主管SUP2。 EMP4有主管SUP3。

SUP1和SUP2反过来拥有相同的老板SUP4。

我现在想创建一个表单，有两个列表框，lb1和lb2。在lb1中我向管理员展示，在lb2中，我向所有员工展示。
然后我选择SUP1表格lb1。 lb2现在只显示EMP1和EMP2，因为它们属于属于 SUP1。

如果我选择lb1中的SUP4，我希望lb2显示EMP1，EMP2，SUP1和SUP2，
因为它们都是SUP4的下属。

奖金问题：

我希望能够在
员工 - 主管结构中使用未知数量的级别。
所以我想创建一个表单......嗯......让我们说5个列表框（我可以自己弄清楚如何动态创建它们......）
然后是一个显示员工的列表框。
第一个列表框，应该显示顶级主管（那些没有主管的主管）。
第二个列表框应该显示所有二级主管，即
主管，但不是那些主管有主管的主管。
等等......
首次打开表格时，所有员工都会显示在列表框中。
如果你在任何列表框中选择一个主管，缩短列表。
如果您选择其中一个上级主管，则另一个组合框xes
和列表框应该缩短。

任何和所有的帮助表示赞赏，

/ jim

I have a problem with this standard employee-supervisor scenario

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

I want to show all employees "belonging" to a specific supervisor.

E.g, EMP1 and EMP2 both has the same supervisor SUP1. EMP3 has a
supervisor SUP2. EMP4 has supervisor SUP3.

SUP1 and SUP2 in turn have the same boss SUP4.

I now want to create a form, with two listboxes, lb1 and lb2.
In lb1 I show the supervisors and in lb2, I show ALL employees.
I then select SUP1 form lb1. lb2 now only shows EMP1 and EMP2 since they
"belong" to SUP1.

If I select SUP4 in lb1, I want lb2 to show EMP1, EMP2, SUP1 and SUP2,
since they are all SUP4''s underlings.

Bonus question:

I want to be able to work with an unknown number of levels in the
employee-supervisor structure.
So I want to create a form with ... hm... lets say 5 listboxes (I can
figure out how to create those dynamically on my own...)
And then a listbox that shows the employees.
The first listbox, should show the top-level supervisor (those that do not
have a supervisor).
The second listbox should show all second-level supervisors, that is,
those who have a supervisor, but not those whose supervisor has a
supervisor.
And so on...
When you first open the form, all employees are shown in the listbox.
If you select a supervisor in any of the listboxes, the list is shortened.
If you select one of the upper-level supervisors both the other comboboxes
and the listbox should be shortened.

Any and all help appreciated,

/jim

" John Welch" < j+ohnw+elch@cal+central.com（删除+'s）> skrev i en

meddelelse新闻：dn ********* @ enews4.newsguy.com ...

"John Welch" <j+ohnw+elch@cal+central.com (remove +''s)> skrev i en
meddelelse news:dn*********@enews4.newsguy.com...

不确定
然后你可以将第二个列表框的行源设置为：
从qryEmployeesPlusSupervisors中选择employeeID，名称
supervisorID =表单！yourform.lstBox1


是的，但那样会只有当我在listbox1中选择一个条目才能工作

希望这有帮助

Not sure about then you could set the second listbox''s rowsource to:
SELECT employeeID, Name from qryEmployeesPlusSupervisors where
supervisorID= forms!yourform.lstBox1
Yes, but that would only work if I select an entry in listbox1
hope this helps

不是真的:-)但是感谢您的尝试。


/ jim

Not really :-) but thanks for trying.

/jim

On Thu，2005年12月15日14:23:53 +0100，Jim Andersen < jb **** @ politi.dk>

写道：

On Thu, 15 Dec 2005 14:23:53 +0100, "Jim Andersen" <jb****@politi.dk>
wrote:

我对这个标准的员工 - 主管情景有疑问

图片：
http：// www。 databasedev.co.uk/self-join_query.html

我想向所有员工展示归属感对于特定的主管。

例如，EMP1和EMP2都有相同的主管SUP1。 EMP3有一名主管
SUP2。 EMP4有主管SUP3。

SUP1和SUP2反过来拥有相同的老板SUP4。

我现在想创建一个表单，有两个列表框，lb1和lb2。在lb1中我向管理员展示，在lb2中，我向所有员工展示。
然后我选择SUP1表格lb1。 lb2现在只显示EMP1和EMP2，因为它们属于属于如果我在lb1中选择SUP4，我希望lb2显示EMP1，EMP2，SUP1和SUP2，因为它们都是SUP4'的下属。

奖金问题：

我希望能够在
员工 - 主管结构中使用未知数量的级别。
所以我想创建一个表单......嗯......让我们说5个列表框（我可以想出如何动态创建它们......）
然后是一个显示员工的列表框。
第一个列表框，应该显示顶级主管（那些没有主管的主管）。
第二个列表框应该显示所有二级主管，即那些拥有主管的人主管，但不是那些主管有主管的主管。
等等......
当您第一次打开表格时，所有员工都会显示在列表框中。
如果您选择主管任何列表框都会缩短列表。
如果您选择其中一个上级主管，其他组合框和listb牛应该缩短。

任何和所有的帮助赞赏，

I have a problem with this standard employee-supervisor scenario

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

I want to show all employees "belonging" to a specific supervisor.

E.g, EMP1 and EMP2 both has the same supervisor SUP1. EMP3 has a supervisor
SUP2. EMP4 has supervisor SUP3.

SUP1 and SUP2 in turn have the same boss SUP4.

I now want to create a form, with two listboxes, lb1 and lb2.
In lb1 I show the supervisors and in lb2, I show ALL employees.
I then select SUP1 form lb1. lb2 now only shows EMP1 and EMP2 since they
"belong" to SUP1.

If I select SUP4 in lb1, I want lb2 to show EMP1, EMP2, SUP1 and SUP2, since
they are all SUP4''s underlings.

Bonus question:

I want to be able to work with an unknown number of levels in the
employee-supervisor structure.
So I want to create a form with ... hm... lets say 5 listboxes (I can figure
out how to create those dynamically on my own...)
And then a listbox that shows the employees.
The first listbox, should show the top-level supervisor (those that do not
have a supervisor).
The second listbox should show all second-level supervisors, that is, those
who have a supervisor, but not those whose supervisor has a supervisor.
And so on...
When you first open the form, all employees are shown in the listbox.
If you select a supervisor in any of the listboxes, the list is shortened.
If you select one of the upper-level supervisors both the other comboboxes
and the listbox should be shortened.

Any and all help appreciated,

奖金回答：做自己的功课。

然而，有了这个说，如果你有所尝试，请展示你的工作并说明你的理由。如果你这样做，你可能会得到一些帮助。


mike

Bonus answer: do your own homework.

However, with that said, if you have attempted something, show your
work and state your rationale. You might get SOME help if you do
that.

mike

这篇关于自我加入。员工和主管查询。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！