Unary Relationships

[EMerlin]

I'm trying to create a report that incorporates a unary relationship. This report obtains data from one table called SalesReps that contain personal information (e.g., Sales Rep ID, First Name, Last Name, etc.). This table also includes a Manager ID. A manager is also a Sales Rep so the Manager ID links back to the Sales Rep ID in the same table. In other words, Manager S0001 is also Sales Rep S0001.

I want to create a report that shows the manager with the sales reps that they manage. The report works fine until I add the Managers Name; the values used for the manager belong to the first sales rep in the group.

I create a group for the Manager and placed the sales reps in the detail area. I also tried creating a query to get the information ... Access still gives me the name for the first salesperson in the group.

I would like to use the Unary Relationship. Any suggestions?

Best wishes,
Emerlin

 

[jzwp22]

What is in the managerID field for those sales reps who are also managers?

 

[jzwp22]

What I would probably do is create a query that gets the unique managerIDs:

query name: qryManagerIDs

SELECT DISTINCT tblSalesReps.ManagerID
FROM tblSalesReps;


Create another query that joins the above query back to the sales rep table and join the managerID to the salesrepID

query name: qryManager

SELECT qryManagerIDs.ManagerID, tblSalesReps.txtFirstName, tblSalesReps.txtLastName
FROM tblSalesReps INNER JOIN qryManagerIDs ON tblSalesReps.pkSalesRepID = qryManagerIDs.ManagerID;


Now create a third query that joins the qryManagers back to the sales rep table (ManagerID of the query to the ManagerID of the table)

query name: qrySalesPeopleWithManagers

SELECT tblSalesReps.pkSalesRepID, tblSalesReps.txtFirstName AS SalesFName, tblSalesReps.txtLastName AS SalesLName, qryManagers.ManagerID, qryManagers.txtFirstName, qryManagers.txtLastName
FROM qryManagers INNER JOIN tblSalesReps ON qryManagers.ManagerID = tblSalesReps.ManagerID;


I would base my report on this last query. Example database attached for reference.

 

[EMerlin]

Thank you so much for this answer and the time it took to put this together. I really appreciate your help and your solution worked fantastic. Your answer and cold were clear and easy to understand, and, I never would have been able to figure this out myself.

A big thanks to jzwp22.

 

[jzwp22]

You're welcome. Glad to help out.