Solved Concatenate values from related records (1 Viewer)

R

rondon

Member
Local time
Today, 23:53
Joined
May 16, 2020
Messages
53
I have a query named qryAgencyAssigned which identifies all the agencies assigned to a Report in table ReportID.
Each ReportID may have many Agencies, and the query reflects this

1594626363889.png


Is it possible to Concatenate all of the agency values into a single text box control source on a form or to do it within a query eg


Thanks Ron
 
G

Galaxiom

Super Moderator
Staff member
Local time
Today, 23:53
Joined
Jan 20, 2009
Messages
12,851
BTW Similar capabilities are available in SQL Server if you are using that as a back end.

Easy-to-use dedicated functionality was introduced in 2019 while it can be fudged in earlier versions using JSON capabilities.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 14:53
Joined
Sep 21, 2011
Messages
14,238
rondon said:
Yes I have study this but found it a little complicated for my level of coding
Click to expand...
Well I don't think you are going to find anything easier than that.?
You pass in a few parameters (2 at least) and that is it.?
 
R

rondon

Member
Local time
Today, 23:53
Joined
May 16, 2020
Messages
53
Gasman said:
Well I don't think you are going to find anything easier than that.?
You pass in a few parameters (2 at least) and that is it.?
Click to expand...
when I look at the code I not quite sure where to put those in
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 14:53
Joined
Sep 21, 2011
Messages
14,238
Code: 
MyConcatenatedControl = ConcatRelated("YourField","YourTable","Field='" & Me.txtControl & "'")
where Me.txtControl is the control holding "2015/16_1"

This is one from a report of mine direct to a control on the report

Code: 
=ConcatRelated("CrewName","QryCrewStandby","QryCrewStandby.Rank = """ & [Rank] & """ AND QryCrewStandby.On_Date = #" & Format([Dated],"mm/dd/yyyy") & "#","CrewNameRev")
Here I get the Crewnames from a query called QryCrewStandby for a required Rank on a required date and sorted by Surname

and the result is
1594639958521.png
 
Isaac

Isaac

Lifelong Learner
Local time
Today, 06:53
Joined
Mar 14, 2017
Messages
8,777
Galaxiom said:
BTW Similar capabilities are available in SQL Server if you are using that as a back end.

Easy-to-use dedicated functionality was introduced in 2019 while it can be fudged in earlier versions using JSON capabilities.
Click to expand...
You're talking about FOR XML PATH I assume? What was the easier, less hacky way recently introduced? I feel I've used it once but cannot remember what it is at the moment.
 
R

rondon

Member
Local time
Today, 23:53
Joined
May 16, 2020
Messages
53
Uncle Gizmo said:
There's a Blog on my website:-

Display Field Data Horizontally

which covers this topic in detail, including a YouTube video, and a sample database you can download for free HERE:- https://gum.co/HorizontalData

Use coupon code:- b0iy3g7
Click to expand...

HI

I looked at the video and web page and it certainly simplified my understanding of the process. I copied the SQL statement from my query and inserted it in to the code (=fConcatList()) which I typed out to get a better understanding of. I set the text field (frmReviewReportID!txtfConcatList) data control source to the code (=fConcatList()) and it returned all of the agency names against all ReportID in the query (qryConcatList).

Up to this point it worked as per the video
----------------------------------------------------------------------------------------------------------------------------------------------
My form (frmReviewReportID) is filtered by ReportID selected from a combo box. I have set this ReportID as the criteria for the query qryConcatList. I have done this by adding the following to the criteria [Forms]![frmReviewReportID]![ReportID]

If I select ReportID 2020_1in the combo box it displays the records for 2020_1 , I would like it to refresh the query and return only those agencies assigned to that ReportID in the query. Then pass these values to text field (txtfConcatList)

I thought by pasting the new SQL statement into fConCatList() with the WHERE criteria would achieve this but sadly I was mistaken:

SELECT tblReportID.ReportID, tblAgencyAssigned.AgencyName
FROM tblReportID INNER JOIN (tblRecommendations INNER JOIN tblAgencyAssigned ON tblRecommendations.RECID = tblAgencyAssigned.RecID) ON tblReportID.ReportID = tblRecommendations.ReportID
WHERE (((tblReportID.ReportID)=[Forms]![frmReviewReportID]![ReportID]));

Once the WHERE criteria was added the fConCatList() code returned an error

1594683620364.png


1594683656339.png


Can you suggest where I have gone wrong and if there is a way around this. My first thoughts were that as the form filters out the ReportID and the selected fields, as the query is not refreshing it causes the error. I am new to VBA so am struggling with this error.
Thanks Ron
 
June7

June7

AWF VIP
Local time
Today, 05:53
Joined
Mar 9, 2014
Messages
5,466
Show the VBA code building the statement for strSQL.
 
G

Galaxiom

Super Moderator
Staff member
Local time
Today, 23:53
Joined
Jan 20, 2009
Messages
12,851
Isaac said:
You're talking about FOR XML PATH I assume?
Click to expand...

I use the FOR JSON PATH variation. Much the same idea. It isn't exactly obvious until you have a good hard look at it and there are a couple of tricks to avoid problems with numbers adding instead of concatenating and associated troubles with Null propagation. It is a bit bewildering when your first attempt comes back with nothing.

I only have SQL 2016 to work with.
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:53
Joined
Jul 9, 2003
Messages
16,274
Gasman said:
Oh, come on Tony, what fun would that be if you actually saw the SQL Statement?
Click to expand...

Well sounds like it might refer to a field named "Date" or some other Reserved Word. But I couldn't tell you until I've seen it...

It would be nice to think, and a bit scary, that one day computers will have artificial intelligence that can answer everyone's MS Access questions. The day will come when you will say I need a database that stores this information and Returns these reports and hey presto the Artificial Intelligence build it for you.. But somehow I doubt it.

Google the premier search company, the company that supposed to know everything about you, why on earth would they serve Me Up video adverts on makeup? I think people's fears about artificial intelligence taking over the world are unfounded, well at least for the foreseeable future... Mind you the fact that they serve me up adverts on makeup demonstrates that these artificial intelligence things are going to make some blooming great mistakes!
 
Isaac

Isaac

Lifelong Learner
Local time
Today, 06:53
Joined
Mar 14, 2017
Messages
8,777
Galaxiom said:
I use the FOR JSON PATH variation. Much the same idea. It isn't exactly obvious until you have a good hard look at it and there are a couple of tricks to avoid problems with numbers adding instead of concatenating and associated troubles with Null propagation. It is a bit bewildering when your first attempt comes back with nothing.

I only have SQL 2016 to work with.
Click to expand...
Ahh, ok. I didn't know about FOR JSON PATH.
Thanks for the info.
 
R

rondon

Member
Local time
Today, 23:53
Joined
May 16, 2020
Messages
53
Uncle Gizmo said:
Well sounds like it might refer to a field named "Date" or some other Reserved Word. But I couldn't tell you until I've seen it...

It would be nice to think, and a bit scary, that one day computers will have artificial intelligence that can answer everyone's MS Access questions. The day will come when you will say I need a database that stores this information and Returns these reports and hey presto the Artificial Intelligence build it for you.. But somehow I doubt it.

Google the premier search company, the company that supposed to know everything about you, why on earth would they serve Me Up video adverts on makeup? I think people's fears about artificial intelligence taking over the world are unfounded, well at least for the foreseeable future... Mind you the fact that they serve me up adverts on makeup demonstrates that these artificial intelligence things are going to make some blooming great mistakes!
Click to expand...
Code Tags Added by UG
Please use Code Tags when posting VBA Code
Please read this for further information:-
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/
Please feel free to Remove this Comment

Code: 
'Please find the code below

Option Compare Database
Option Explicit

Public Function fConcatList() As String
Dim DB As DAO.Database
Dim RS As DAO.Recordset

Dim strSQL As String
Dim strtext As String

strSQL = "Select FROM tblReportID INNER JOIN (tblRecommendations INNER JOIN tblAgencyAssigned ON tblRecommendations.RECID = tblAgencyAssigned.RecID) ON tblReportID.ReportID = tblRecommendations.ReportID WHERE (((tblReportID.ReportID)=[Forms]![frmReviewReportID]![ReportID]));"



Set DB = CurrentDb
    Set RS = DB.OpenRecordset(strSQL, dbOpenForwardOnly)

    Do Until RS.EOF
    If strtext = " " Then
         strtext = RS!AgencyName
         Else
         strtext = strtext & ", " & RS!AgencyName
     End If
    RS.MoveNext
       
        Loop
    RS.Close
    Set RS = Nothing
    Set DB = Nothing
   
    Let fConcatList = strtext
   
    End Function
 
Last edited by a moderator:
You must log in or register to reply here.

Similar threads

P
The Deep State
Replies
9
Views
465
Isaac
Isaac
Mr Ancovy
  • Solved
Mailmerge mess
2
Replies
38
Views
1,684
Gasman
Gasman
S
Hello from Nevada, USA
Replies
8
Views
512
The_Doc_Man
The_Doc_Man
H
avoiding repetitive records in CurrentDb.Execute InsertQuery
Replies
11
Views
345
ebs17
E
C
Sub to update control source of multiple reports and fields within
Replies
3
Views
200
theDBguy
theDBguy

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom