Duplicate Rows

[Ringers]

I am trying to get all staff and there supervisor. the staff ID and supervisor id are the same numbers in different columns. so staff johm smith id 23 has supervisor roger id 22.

this query works and shows the results i want but each row duplicated mant times?

SELECT View_User_Properties_Master.User_id, View_User_Properties_Master.[First Name], View_User_Properties_Master.[Last Name], View_User_Properties_Master.Supervisor, View_User_Properties_Master.gen_Approvelimit, View_User_Properties_Master.Department,
View_User_Properties_Master.Division, View_userproperties.User_id AS SPID, View_userproperties.gen_Firstname AS SPFN,
View_userproperties.gen_Lastname AS SPLN

FROM
View_User_Properties_Master INNER JOIN
View_userproperties ON View_User_Properties_Master.Supervisor = View_userproperties.User_id CROSS JOIN [All Users Plus]

 

[SimonSellick]

I'm not sure what the second table (View_userproperties) or the CROSS JOIN is doing in there - it returns all possible combinations of rows in the two tables. Have you got two views on one table? If so, this might be a bit closer to what you need...

SELECT
  emp.User_id
, emp.[First Name]
, emp.[Last Name]
, emp.Supervisor
, emp.gen_Approvelimit
, emp.Department
, emp.Division
, sup.User_id AS SPID
, sup.[First Name] AS SPFN
, sup.[Last Name] AS SPLN
FROM View_User_Properties_Master emp
INNER JOIN View_User_Properties_Master sup
  ON sup.User_id = emp.Supervisor

...it's hard to be sure without knowing the underlying data. Also, do all the staff you are interested in have a supervisor? If not, you need to alter the INNER JOIN to LEFT OUTER JOIN so that those without a supervisor are included.

This looks like an Access query - is this the correct forum?

HTH
Simon