Hi all.

I have the following problem.

I have a table of contacts with a PK of contactID and various fields including ManagerID.

Some of these contacts are managers in which case this field is 0. otherwise it contains the ContactID of the manager.

What I want to do is query/report so that the manager is shown along with their subordinates.

Does this make sense and can anyone help?

Andy G

Try this:

SELECT tblMgt.ContactName AS Manager, tblContacts.ContactName AS SubOrdinate
FROM tblContacts
RIGHT JOIN tblContacts AS tblMgt ON tblContacts.ManagerID = tblMgt.ContactID
WHERE tblMgt.ManagerID = 0


This gives you a list of all of the Managers and any subordinates they have.

Hi

Thanks for yur help but it doesn't solve the problem.

I only have 1 table called tblContacts and if the contact is not a manager ie works for a manager the manager id field is set to the contactID of the manager. If you are a manager then your manager ID is 0.

Andy

This would probably work better with multiple tables and a different structure, but for now, one quick and dirty solution would be:

SELECT tblContacts.Name AS Manager, tblContacts_1.Name AS Subordinate
FROM tblContacts AS tblContacts_1 INNER JOIN tblContacts ON tblContacts_1.ManagerID=tblContacts.ContactID;

That should give you two columns, the first with the manager name and the second with the subordinate. Mine came up in order, but if not, you may have to order the table by the ContactID field on tblContacts.

Hope that helps

Mike

Mike
EchoVue.com

Odd structure. You probably want TWO tables. One of managers with their IDs and one of just employees with the manager ID who they work for. Having all in one table is odd because if each manager gets ID of zero, then how do you relate THAT manager to his/her subordinate? All the other managers are zero, too!

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thanks Guys

I've changed the data structure, bit of tidying up the old data, all well, hopefully will make everything easier.

Andy

didnt bother to try the code then? It does only use one table, since that is all you specified... tblContacts self joins tblContacts renamed as tblMgt purely to make it look pretty and to help you understand it in future.

If you check out Echovue's code it is pretty much the same as mine but the second table is renamed tblContacts_1 (default for access)... which could (in my experience) become ambiguous, so tblMgt may be more suitable. Echovue's code does differ slightly from mine in so far as mine is using a different kind of join... mine will return all employees with any subordinates they have, Echovue's should return only those employees with subordinates.

Hi

Yes I did try the code, both examples, and realised that you had aliased the table name only after trying to get it to work.

But both of you said that the structure was wrong, so thought it best to change it to save any problems later.

Thanks again.

sorry - bit tetchy this morning!

leehambly's code was good stuff. I simulated it, and it worked nicely. I would opt for keeping the original structure and using his code. Sounds to me like you will be complicating your life by splitting stuff up. Besides, I've seen "Manager/Employee" examples in Wrox books that use your original structure.


K.I.S.S.

"Life is a Database"