Field from table in same table

[Guest]

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

 

[Guest]

Just to clarify Report_To is FirstName & Surname, the same format as Name[F&S]

 

[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

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

 

[Guest]

Hi

Its a text field, same as Name


thanks
--

_______________________
Naz,
London

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

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

 

[Lynn Trapp]

Hi

Its a text field, same as Name


thanks

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;