How do I concatenate a field from multiple records? (1 Viewer)

RbTrail

Registered User.
Local time
Today, 14:09
Joined
Mar 29, 2019
Messages
14
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.
 

Attachments

  • qryEmpDept.png
    qryEmpDept.png
    9.5 KB · Views: 66
  • qryEmpDept_result.png
    qryEmpDept_result.png
    56.9 KB · Views: 64

theDBguy

I’m here to help
Staff member
Local time
Today, 12:09
Joined
Oct 29, 2018
Messages
21,358
Or like this. Sorry, can't resist. ;)
 

RbTrail

Registered User.
Local time
Today, 14:09
Joined
Mar 29, 2019
Messages
14
Thank you both! I knew it had to be simple.
 

GinaWhipp

AWF VIP
Local time
Today, 15:09
Joined
Jun 21, 2011
Messages
5,901
Hmm, change this

Code:
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)

to...

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

theDBguy

I’m here to help
Staff member
Local time
Today, 12:09
Joined
Oct 29, 2018
Messages
21,358
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?
Code:
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

Registered User.
Local time
Today, 14:09
Joined
Mar 29, 2019
Messages
14
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’m here to help
Staff member
Local time
Today, 12:09
Joined
Oct 29, 2018
Messages
21,358
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.
 

Users who are viewing this thread

Top Bottom