Relating Fields in the Same Table


G

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?
 
Ad

Advertisements

J

Joseph Meehan

Jason said:
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?
 
G

Guest

Joseph Meehan said:
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
 
Ad

Advertisements

V

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top