How do I concatenate a field from multiple records?

[RbTrail]

I am attempting to create a query that contains a field that lists the employee departments. The goal is to use this query in report headers, ie:

EMPLOYEE: 1111 DEPARTMENT: A, B, C
detail, detail, detail, etc

I've concatenated the Employee names before, but I'm not sure how that works when I'm refering to the same field in multiple records. I've attached screen shots of the query in question.

 

[pbaldy]

Like this?

http://allenbrowne.com/func-concat.html

 

[theDBguy]

Or like this. Sorry, can't resist.

 

[RbTrail]

Thank you both! I knew it had to be simple.

 

[pbaldy]

No problem.

 

[GinaWhipp]

Hmm, change this

Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)

to...

Set rs = db.OpenRecordset([B]strCSV[/B], dbOpenSnapshot)

 

[theDBguy]

Okay, I can't get this code to work. I keep getting this error:

Error 3061: Too few Parameters. Expected 1.
...


Here is the SQL statement I'm trying to run:
SELECT tblEmployee.EmpID, SimpleCSV("SELECT DeptName FROM tblDepartment WHERE tblDepartment.DeptID= " & [tblEmpDept].[DeptID]) AS ["Department(s)"]
FROM tblEmployee INNER JOIN (tblDepartment INNER JOIN tblEmpDept ON tblDepartment.DeptID = tblEmpDept.DeptID) ON tblEmployee.EmpID = tblEmpDept.EmpID;

Hi. Sorry to hear that. I can't tell if the problem is with the function or the query itself because of the use of quotes in the column alias. What happens if you tried it this way?

SELECT tblEmployee.EmpID, SimpleCSV("SELECT DeptName FROM tblDepartment  WHERE tblDepartment.DeptID= " & [tblEmpDept].[DeptID]) AS Departments
FROM tblEmployee INNER JOIN (tblDepartment INNER JOIN tblEmpDept ON  tblDepartment.DeptID = tblEmpDept.DeptID) ON tblEmployee.EmpID =  tblEmpDept.EmpID;

 

[RbTrail]

I found the error in my SQL statement. I forgot to reference the INNER JOIN in the FROM part of the SimpleCSV(). This is the corrected SQL:


SELECT DISTINCT tblEmployee.EmpID, SimpleCSV("SELECT DeptName FROM tblDepartment INNER JOIN tblEmpDept ON tblDepartment.DeptID = tblEmpDept.DeptID WHERE tblEmpDept.EmpID= " & [tblEmployee].[EmpID]) AS [Department(s)]
FROM tblEmployee INNER JOIN (tblDepartment INNER JOIN tblEmpDept ON tblDepartment.DeptID = tblEmpDept.DeptID) ON tblEmployee.EmpID = tblEmpDept.EmpID;

 

[theDBguy]

I found the error in my SQL statement. I forgot to reference the INNER JOIN in the FROM part of the SimpleCSV(). This is the corrected SQL:


SELECT DISTINCT tblEmployee.EmpID, SimpleCSV("SELECT DeptName FROM tblDepartment INNER JOIN tblEmpDept ON tblDepartment.DeptID = tblEmpDept.DeptID WHERE tblEmpDept.EmpID= " & [tblEmployee].[EmpID]) AS [Department(s)]
FROM tblEmployee INNER JOIN (tblDepartment INNER JOIN tblEmpDept ON tblDepartment.DeptID = tblEmpDept.DeptID) ON tblEmployee.EmpID = tblEmpDept.EmpID;

Ah, congratulations! Glad to hear you got it sorted out. Good luck with your project.