concat multiple records? not sure 1

[simon551]

Hi,
Not sure that's the right title for my question.

I have 2 tables: tblEmployees and tblClients

tblClients has a field for "manager" which is related to the tblEmployees.

An employee can be in multiple clients.

What I am trying to do is pull a query that would list all of the clients an employee manages, but all in one field.

For example, if employee 1 manages clients a, b, and c I would like the result to be 1 - a,b,c

This is what I've tried, but it seems there must be a better way.

SELECT tblEmployees.EmpFName, [First] & IIf([Second],", " & [Second],"") AS Accounts
FROM (tblEmployees LEFT JOIN q1 ON tblEmployees.EmpID=q1.EmpID) LEFT JOIN q2 ON tblEmployees.EmpID=q2.EmpID;

q1
SELECT tblClients.EmpID_RelationshipHolder AS EmpID, First(tblClients.ClientName) AS FirstOfClientName, First(tblClients.ClientID) AS [First]
FROM tblEmployees INNER JOIN tblClients ON tblEmployees.EmpID = tblClients.EmpID_
GROUP BY tblClients.EmpID_;

q2
SELECT tblClients.EmpID_ AS EmpID, First(tblClients.ClientName) AS FirstOfClientName, First(tblClients.ClientID) AS [Second]
FROM tblClients LEFT JOIN q1 ON tblClients.ClientID = q1.First
GROUP BY tblClients.EmpID_, q1.First
HAVING (((tblClients.EmpID_) Is Not Null) AND ((q1.First) Is Null));

The main problem with this approach is that there could be unlimited numbers of accounts. The most right now is six, but that could change tomorrow.

Thanks in advance,

-s

 

[PHV]

Have a look here: faq701-4233

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[simon551]

Thank you once again PHV! that program rocks!