Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-15-2019, 01:07 PM   #1
RbTrail
Newly Registered User
 
Join Date: Mar 2019
Location: Southeast, USA
Posts: 14
Thanks: 16
Thanked 1 Time in 1 Post
RbTrail is on a distinguished road
How do I concatenate a field from multiple records?

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.
Attached Images
File Type: png qryEmpDept.png (9.5 KB, 9 views)
File Type: png qryEmpDept_result.png (56.9 KB, 8 views)

RbTrail is offline   Reply With Quote
Old 05-15-2019, 01:08 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,874
Thanks: 12
Thanked 4,033 Times in 3,970 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: How do I concatenate a field from multiple records?

Like this?

http://allenbrowne.com/func-concat.html
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
RbTrail (05-15-2019)
Old 05-15-2019, 01:32 PM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,040
Thanks: 36
Thanked 722 Times in 705 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: How do I concatenate a field from multiple records?

Or like this. Sorry, can't resist.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
RbTrail (05-15-2019)
Old 05-15-2019, 01:49 PM   #4
RbTrail
Newly Registered User
 
Join Date: Mar 2019
Location: Southeast, USA
Posts: 14
Thanks: 16
Thanked 1 Time in 1 Post
RbTrail is on a distinguished road
Re: How do I concatenate a field from multiple records?

Thank you both! I knew it had to be simple.
RbTrail is offline   Reply With Quote
Old 05-15-2019, 01:50 PM   #5
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,874
Thanks: 12
Thanked 4,033 Times in 3,970 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: How do I concatenate a field from multiple records?

No problem.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 05-21-2019, 07:32 AM   #6
GinaWhipp
AWF VIP
 
GinaWhipp's Avatar
 
Join Date: Jun 2011
Location: Ohio, USA
Posts: 5,292
Thanks: 20
Thanked 875 Times in 860 Posts
GinaWhipp has a spectacular aura about GinaWhipp has a spectacular aura about
Re: How do I concatenate a field from multiple records?

Hmm, change this

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

Code:
Set rs = db.OpenRecordset(strCSV, dbOpenSnapshot)
__________________
Gina Whipp
Microsoft MVP (Access 2010-2015)


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GinaWhipp is offline   Reply With Quote
The Following User Says Thank You to GinaWhipp For This Useful Post:
RbTrail (05-21-2019)
Old 05-21-2019, 07:56 AM   #7
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,040
Thanks: 36
Thanked 722 Times in 705 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: How do I concatenate a field from multiple records?

Quote:
Originally Posted by RbTrail View Post
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;

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
RbTrail (05-21-2019)
Old 05-21-2019, 08:10 AM   #8
RbTrail
Newly Registered User
 
Join Date: Mar 2019
Location: Southeast, USA
Posts: 14
Thanks: 16
Thanked 1 Time in 1 Post
RbTrail is on a distinguished road
Re: How do I concatenate a field from multiple records?

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;
RbTrail is offline   Reply With Quote
Old 05-21-2019, 08:16 AM   #9
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,040
Thanks: 36
Thanked 722 Times in 705 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: How do I concatenate a field from multiple records?

Quote:
Originally Posted by RbTrail View Post
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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
RbTrail (05-21-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Possible to concatenate multiple records grouped by date? (AC2007) AOB Queries 3 05-02-2014 09:23 AM
Concatenate fields in multiple records into one record Seadog Queries 4 02-14-2012 06:56 PM
Concatenate multiple records into one textbox JamesMcS Reports 5 06-07-2010 05:54 AM
Concatenate multiple records into memo field detrie Queries 8 03-11-2007 09:48 AM
concatenate multiple records into one text field database Forms 3 06-24-2004 11:05 AM




All times are GMT -8. The time now is 05:10 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World