Field from table in same table

N

Naz

Hi

I have a table (tbl_employees) with the following fields

ID
[F]irstName
urname
Name [F&S]
Reports_To

All the reports_to people are in the same table. I'm trying to create a
query with all the above fields but also a ID_Of_Reports_To field. I'm not
having much luck and keep going round in circles. I've tried adding the table
twice and joining name to name and dragging in ID from the secod table and
that sort of thing.

Can anyone help.....thanks in advance
 
L

Lynn Trapp

Hi

I have a table (tbl_employees) with the following fields

ID
[F]irstName
urname
Name [F&S]
Reports_To

All the reports_to people are in the same table. I'm trying to create a
query with all the above fields but also a ID_Of_Reports_To field. I'm not
having much luck and keep going round in circles. I've tried adding the table
twice and joining name to name and dragging in ID from the secod table and
that sort of thing.

Can anyone help.....thanks in advance
Click to expand...


Is the Reports_To field a Long Integer field that stores the ID of the
person each record is reporting to?

Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
 
N

Naz

Hi

Its a text field, same as Name


thanks
--

_______________________
Naz,
London


Lynn Trapp said:
Hi

I have a table (tbl_employees) with the following fields

ID
[F]irstName
urname
Name [F&S]
Reports_To

All the reports_to people are in the same table. I'm trying to create a
query with all the above fields but also a ID_Of_Reports_To field. I'm not
having much luck and keep going round in circles. I've tried adding the table
twice and joining name to name and dragging in ID from the secod table and
that sort of thing.

Can anyone help.....thanks in advance
Click to expand...


Is the Reports_To field a Long Integer field that stores the ID of the
person each record is reporting to?

Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Click to expand...
 
L

Lynn Trapp

Hi

Its a text field, same as Name


thanks
Click to expand...

Then give this a try:

SELECT A.ID,
A.FirstName,
A.Surname,
Name,
(SELECT X.Reports_To
From tbl_employees AS X
Where X.Reports_To = A.Name) AS ReportsTo
From tbl_employees AS A;
 
Top