Relating Fields in the Same Table

[Guest]

I have a personnel database that uses a combo box to select a supervisor from
a list of names on the same table. I want to be able to create a report that
displays a list of supervisors full name (multiple fields) with thier
subordinates full name. How can I link the names together so I can display
the relational fields together?

 

[Joseph Meehan]

I have a personnel database that uses a combo box to select a
supervisor from a list of names on the same table. I want to be able
to create a report that displays a list of supervisors full name
(multiple fields) with thier subordinates full name. How can I link
the names together so I can display the relational fields together?

Could you start by describing the table(s) you have now with a list of
the fields?

 

[Guest]

Could you start by describing the table(s) you have now with a list of
the fields?

So far, I only have 1 table [Personnel]. The relevant fields I have are
ID(Autonumber); Rank; FirstName; LastName; MI; Supervisor

 

[Van T. Dinh]

It may be more convenient to have a Boolean Field "IsSupervisor" then you
can create a self-join Query to return rows with each row shoeing the
Supervisor and one of his staff. The Query should be something like:

SELECT SUP.[LastName] & ", " & SUP.[FirstName] As Supervsr,
STA.[LastName] & ", " & STA.[FirstName] As Staff
FROM Personnel AS SUP INNER JOIN
Personnel AS STA ON SUP.ID = STA.Supervisor
WHERE SUP.IsSupervisor = True

In the Report, you use the grouping on the (calculated) Field [Supervsr] to
list the Supervisor & his/her staff.

It can be done without the IsSupervisor Field but it is much harder.