Creating self-join table

I'm trying to create a self-join table to show the relationship between
employee and supervisor. In another thread, I was advised to create a
SupervisorID in the employee table, a separate Supervisor table, and
join the Supervisor table to the Employee table and a copy of the
Supervisor table to create the self-join. I can't figure out how to do
this from reading Viescas or from researching it here. I can start a
query and create 2 copies of the Employee table, but it seems to me
that the table I want between them is the one created by the self-join.
It would have supervisor and employee(s), which is what the self-join
query is to create.
I also tried just joining 2 copies of the Employee table from
EmployeeID (PK) to SupervisorID in the other copy, but got a very
awkward table with employee's names in the left columns and suprvisors
and employee's names in the right columns with lots of blanks. Very
hard to read, though it did show each employee's supervisor. Just not
in any useful format.
I don't know SQL, which I'm gathering from research here is the
language of Access. I need an explanation in something clearer than SQL
shorthand, if possible. I know I'm asking a lot. (I'm getting signed up
for an Access class soon, which will help.) I need it in terms of
create a table with these columns, create a join by dragging from row x
in table a to row y in table b and select option 2 in the joins box.
That sort of thing.
My boss wants this and I've been working on it quite a while. Can
someone help me in plain English?

re: Creating self-join table

>I also tried just joining 2 copies of the Employee table from[color=blue]
>EmployeeID (PK) to SupervisorID in the other copy, but got a very
>awkward table with employee's names in the left columns and suprvisors
>and employee's names in the right columns with lots of blanks. Very
>hard to read, though it did show each employee's supervisor. Just not
>in any useful format.[/color]

This sounds like the approach I'd actually use, so can you explain why it
isn't in a useful format? What is the format you are after?
[color=blue]
>I don't know SQL, which I'm gathering from research here is the
>language of Access. I need an explanation in something clearer than SQL
>shorthand, if possible. I know I'm asking a lot. (I'm getting signed up
>for an Access class soon, which will help.) I need it in terms of
>create a table with these columns, create a join by dragging from row x
>in table a to row y in table b and select option 2 in the joins box.
>That sort of thing.[/color]

Happy to do that, but the reason why people post SQL is because it's more
precise than trying to explain what to drag where and why. Did you know that
you can just cut and paste SQL into a query? When you are in Query Designer,
the top left button shows a datagrid, and if you click on it, the query runs
and the results are displayed. The button then displays a tri-square, and if
you click on that, it takes you back to Query Designer. However, you can also
click on the drop down next to the button, and it will show another option
called "SQL". If you click on that, the undeerlying SQL for the query will be
displayed. At that point, you can paste in the SQL that people post here or
elsewhere, and then you can swithc back to the Query Designer view to see
what it looks like in there. The SQL itself can be quite daunting, but when
you see it in Query Designer, it can make a lot more sense.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200509/1

re: Creating self-join table

David S via AccessMonster.com wrote:[color=blue][color=green]
> >I also tried just joining 2 copies of the Employee table from
> >EmployeeID (PK) to SupervisorID in the other copy, but got a very
> >awkward table with employee's names in the left columns and suprvisors
> >and employee's names in the right columns with lots of blanks. Very
> >hard to read, though it did show each employee's supervisor. Just not
> >in any useful format.[/color]
>
> This sounds like the approach I'd actually use, so can you explain why it
> isn't in a useful format? What is the format you are after?[/color]

Thanks for your reply.
The table created by the query contains the first name, last name and
employee ID of each employee, followed by the same information for
their supervisor. the problem is that every employee appears in both
sets of columns, including those that are not supervisors appearing in
the "supervisor" set of columns. So most of the names in the supervisor
columns have no corresponding name in the "employee" columns, just
blanks.
What I'd rather have is 2 views. One listing each employee and a
subdatasheet of their supervisor, and another of supervisors only, with
a subdatasheet of the employees they supervise.[color=blue]
>[color=green]
> >I don't know SQL, which I'm gathering from research here is the
> >language of Access. I need an explanation in something clearer than SQL
> >shorthand, if possible. I know I'm asking a lot. (I'm getting signed up
> >for an Access class soon, which will help.) I need it in terms of
> >create a table with these columns, create a join by dragging from row x
> >in table a to row y in table b and select option 2 in the joins box.
> >That sort of thing.[/color]
>
> Happy to do that, but the reason why people post SQL is because it's more
> precise than trying to explain what to drag where and why. Did you know that
> you can just cut and paste SQL into a query? When you are in Query Designer,
> the top left button shows a datagrid, and if you click on it, the query runs
> and the results are displayed. The button then displays a tri-square, and if
> you click on that, it takes you back to Query Designer. However, you can also
> click on the drop down next to the button, and it will show another option
> called "SQL". If you click on that, the undeerlying SQL for the query will be
> displayed. At that point, you can paste in the SQL that people post here or
> elsewhere, and then you can swithc back to the Query Designer view to see
> what it looks like in there. The SQL itself can be quite daunting, but when
> you see it in Query Designer, it can make a lot more sense.[/color]

I understand why they use it. And if that's a condition for getting
help here, I'll accept that and try and find help elsewhere, since I
don't plan on learning SQL in the forseeable future. I'm in the middle
of learning Excel VBA and Access, and my capacity to absorb new
material is being stretched as it is. Oh yes, I just started a new job
in an entirely new field and am trying to figure out what I'm doing
each day. So I have a new job to do while I'm learning all this. I am
in the process of signing up for a 2 day beginner class in Access. I'm
sure it will help, and I'll bring lots of specific questions, like how
do I create a self-join in Access. I don't really want to create it
using SQL unless I have to. I understand the intricasies and pitfalls
of writing code, and just don't want the complications in my work right
now, at least not the additional ones![color=blue]
>
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...ccess/200509/1[/color]

re: Creating self-join table

>I understand why they use it. And if that's a condition for getting[color=blue]
>help here, I'll accept that and try and find help elsewhere, since I
>don't plan on learning SQL in the forseeable future.[/color]

It's not a precondition, it's just that it will be harder to give you help
that you will be able to understand & implement. It's certainly not
impossible, and I'll try, but we may have some back & forth as we try to
clarify what exactly each of us means by a particular statement :) It does
sound like you've got a lot on your plate, but if you do get a chance, I'd
say SQL is definitely well worth learning, since you can use it not only for
Access, but pretty much any relational database out there. But back to your
immediate requirements:
[color=blue]
>The table created by the query contains the first name, last name and
>employee ID of each employee, followed by the same information for
>their supervisor. the problem is that every employee appears in both
>sets of columns, including those that are not supervisors appearing in
>the "supervisor" set of columns. So most of the names in the supervisor
>columns have no corresponding name in the "employee" columns, just
>blanks.[/color]
Mm - the employees should only appear in the left hand set of columns, and
only supervisors should appear multiple times in the right hand set of
columns. I suspect the join between the two versions of the same table hasn't
been defined properly. In the Query Designer, one version of the table should
appear as "Employee" and the other as "Employee_1". There should be a join or
line from SupervisorID in Employee to EmployeeID in Employee_1. If the line
has dots at both ends rather than an arrow, it is an "inner join", meaning
that only those Employees with a Supervisor will be displayed. If you want
all Employees to be shown irrespective of whether they have a Supervisor, you
need a "Left Outer Join" - right click on the join line and choose "Join
Properties", and then choose "Include ALL records from 'Employee' etc". This
should give you all Employees and who their Supervisor is.
[color=blue]
>What I'd rather have is 2 views. One listing each employee and a
>subdatasheet of their supervisor, and another of supervisors only, with
>a subdatasheet of the employees they supervise.[/color]
I'm afraid I don't understand what you mean by "subdatasheet". The query
above will produce the employees with their supervisors; you can make it
supervisor centric by sorting it by SupervisorID in Employee first. Then, you
can write a report for the output the hides the duplicate entries in the
query. If you want to display the output on a table in a form, I'm afraid
you're going to need to ask over in the Forms or Forms Programming forum -
I'm not very good at that part of the MS Access world...


--
Message posted via http://www.accessmonster.com

re: Creating self-join table

David, Thanks for your reply. I did exactly what you said, and it
worked great! I now have a table that lists all employees in the left
columns, and supervisors only on the right. I had the join wrong. I
want to reply to your message more specifically:

David S via AccessMonster.com wrote:[color=blue][color=green]
> >I understand why they use it. And if that's a condition for getting
> >help here, I'll accept that and try and find help elsewhere, since I
> >don't plan on learning SQL in the forseeable future.[/color]
>
> It's not a precondition, it's just that it will be harder to give you help
> that you will be able to understand & implement. It's certainly not
> impossible, and I'll try, but we may have some back & forth as we try to
> clarify what exactly each of us means by a particular statement :) It does
> sound like you've got a lot on your plate, but if you do get a chance, I'd
> say SQL is definitely well worth learning, since you can use it not only for
> Access, but pretty much any relational database out there. But back to your
> immediate requirements:[/color]

I should explain, I'm not a database person, I'm an Excel expert. The
only reason I'm doing this project in Access is that my boss decided I
can learn any software quickly and easily and she asked me to do this
employee database. I think I've showed her with this project that her
she was wrong about my capabilities!
[color=blue]
>[color=green]
> >The table created by the query contains the first name, last name and
> >employee ID of each employee, followed by the same information for
> >their supervisor. the problem is that every employee appears in both
> >sets of columns, including those that are not supervisors appearing in
> >the "supervisor" set of columns. So most of the names in the supervisor
> >columns have no corresponding name in the "employee" columns, just
> >blanks.[/color]
> Mm - the employees should only appear in the left hand set of columns, and
> only supervisors should appear multiple times in the right hand set of
> columns. I suspect the join between the two versions of the same table hasn't
> been defined properly. In the Query Designer, one version of the table should
> appear as "Employee" and the other as "Employee_1". There should be a join or
> line from SupervisorID in Employee to EmployeeID in Employee_1. If the line
> has dots at both ends rather than an arrow, it is an "inner join", meaning
> that only those Employees with a Supervisor will be displayed. If you want
> all Employees to be shown irrespective of whether they have a Supervisor, you
> need a "Left Outer Join" - right click on the join line and choose "Join
> Properties", and then choose "Include ALL records from 'Employee' etc". This
> should give you all Employees and who their Supervisor is.[/color]

That's what I have now. Great!
[color=blue]
>[color=green]
> >What I'd rather have is 2 views. One listing each employee and a
> >subdatasheet of their supervisor, and another of supervisors only, with
> >a subdatasheet of the employees they supervise.[/color]
> I'm afraid I don't understand what you mean by "subdatasheet". The query
> above will produce the employees with their supervisors; you can make it
> supervisor centric by sorting it by SupervisorID in Employee first. Then, you
> can write a report for the output the hides the duplicate entries in the
> query. If you want to display the output on a table in a form, I'm afraid
> you're going to need to ask over in the Forms or Forms Programming forum -
> I'm not very good at that part of the MS Access world...[/color]

Maybe you have an older version of Access, or just haven't used that
feature. I have 2000 and it has the capability to show a datasheet
where each record has a + or - next to it. If it has a +, I can click
on the + and a subdatasheet appears under the record with whatever
table I've chosen. I've tried it with the new datasheet your query
created and it's the entire datasheet, not just the data from the
selected record, which is what I want in it. So I'll have to do more
work on that.[color=blue]
>
>
> --
> Message posted via http://www.accessmonster.com[/color]

I appreciate your patience and help.

re: Creating self-join table

>Maybe you have an older version of Access, or just haven't used that[color=blue]
>feature. I have 2000 and it has the capability to show a datasheet
>where each record has a + or - next to it. If it has a +, I can click
>on the + and a subdatasheet appears under the record with whatever
>table I've chosen. I've tried it with the new datasheet your query
>created and it's the entire datasheet, not just the data from the
>selected record, which is what I want in it. So I'll have to do more
>work on that.[/color]

I do have Access 2000 - it sounds like the feature I've seen before when two
tables have a defined relationship, rather than just being joined in a query.
I've not done much with it, though.
[color=blue]
>I appreciate your patience and help.[/color]

Glad to have been of help - hope you get to the end of this without your
brain exploding :)


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200509/1

re: Creating self-join table

David S via AccessMonster.com wrote:[color=blue][color=green]
> >Maybe you have an older version of Access, or just haven't used that
> >feature. I have 2000 and it has the capability to show a datasheet
> >where each record has a + or - next to it. If it has a +, I can click
> >on the + and a subdatasheet appears under the record with whatever
> >table I've chosen. I've tried it with the new datasheet your query
> >created and it's the entire datasheet, not just the data from the
> >selected record, which is what I want in it. So I'll have to do more
> >work on that.[/color]
>
> I do have Access 2000 - it sounds like the feature I've seen before when two
> tables have a defined relationship, rather than just being joined in a query.
> I've not done much with it, though.
>[color=green]
> >I appreciate your patience and help.[/color]
>
> Glad to have been of help - hope you get to the end of this without your
> brain exploding :)[/color]

Too late! :)