Subform based on one table

[miaplacidus]

I have an employees list. The list includes a field for
who the employee reports to. I have a form that shows each
employee with a subform which shows all of his
subordinates. Both the form and the subform are based on
the employees table. (I use a query on the employees table
for the subform.)

The employees subform shows the name of the subordinate
his title and who he reports to. The employees subform has
an extra row in it that begins with an asterisk. In
the "Reports to" column of that row the field is filled in
with the words EmployeesList, which happens to be the name
of the form.

How can I make the "EmployeesList" entry go away and how
can I make the default entry for that field in the subform
to be the same as the "Name" field in the parent form? The
way this works is that the "Name" field in the parent
field is linked to the "Reports to" field in the sub-form.

Each employee in the employee table only reprts to on
supervisor, but each supervisor may have several employees
that report to him: therefore the employee table has a one
to many relationship with itself!

I cold have a separate table for all the supervised
employees, but then I'd have un necessary double entries.

This ought to be easy, but I'm out of practice.

 

[Allen Browne]

Your Staff table should have a primary key, and a foreign key for the person
they report to. The fields will be something like this:
- StaffID AutoNumber primary key
- Surname Text
- FirstName Text
- ReportsToID Number foreign key
....

Now open the Relationships window (Tools menu), and drag 2 copies of the
Staff table in. Access will alias the second one as Staff_1. Drag
Staff.StaffID onto Staff_1.ReportsToID, and create the relationship with
Referential Integrity. (This is a "self-join", i.e. the table has a
relationship to itself.)

Once you have that structure in place, open your main form in design view.
Right-click the edge of the subform control, and choose Properties. The
title bar of the Properties box should indicate that you are looking at the
"Subform/Subreport control". On the Data tab of the Properties, box, set
these properties:
LinkMasterFields StaffID
LinkChildFields ReportsToID
This causes Access to match the subform's value of ReportsToID to the
StaffID in the main form, i.e. the subform shows the people who report to
the person in the main form.