Solved Concatenate values from related records (2 Viewers)

[June7]

In future, please post code between CODE tags to retain indentation and readability.

Try concatenating control reference:

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]))

If you need more help, suggest providing db for analysis.

 

[Uncle Gizmo]

This is a time indexed URL showing you the particular part of the video I'm referring to.

Display Field Data Horizontally - Nifty Access

If you notice, in the YouTube, I mention that the field you want to return in the Recordset loop MUST appear in the SQL Statement you are creating the the Recordset Loop from.

'You Have:-
strSQL = "Select FROM tblReportID INNER JOIN

You are trying to call the field here:-
strtext = RS!AgencyName

'Change this SQL TO:-
strSQL = "Select AgencyName FROM tblReportID INNER JOIN

strtext = RS!AgencyName

'alternatively change the SQL TO:-
strSQL = "Select * AgencyName FROM tblReportID INNER JOIN

'The STAR "*" will return all the fields. Although this is not considered good practice because you are shifting around loads of data which you don't necessarily need to do.

 

[rondon]

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

'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

Please find a condense version of the DB

I placed your code into my module and the same error message occurred when I opened form frmReviewReportID. This is the form that has the field txtConcatList() where I would like the agency names passed to.

I hope this helps
Thanks Ron

 

[June7]

This works:

strSQL = "Select 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] & "'"

 

[theDBguy]

Please find a condense version of the DB

Hi Ron,

Please pardon me for jumping in. Please see attached modified version of your db and open qrySimpleCSV. Hope that helps...

 

[rondon]

This works:

strSQL = "Select 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] & "'"

T

This works:

strSQL = "Select 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] & "'"

Thank you this worked, as did the solution by the DBguy. I will try to use this example as I can understand it a bit clearer. However I still have one issue, if the same agency is assign to a ReportID more than once it displays serval times in the field. Is there a way we can not show duplicate strings.

 

[theDBguy]

T


Thank you this worked, as did the solution by the DBguy. I will try to use this example as I can understand it a bit clearer. However I still have one issue, if the same agency is assign to a ReportID more than once it displays serval times in the field. Is there a way we can not show duplicate strings.

View attachment 83552

Hi. If you try my example, just add the DISTINCT keyword to the SELECT statement. For example,

SimpleCSV("SELECT DISTINCT...

 

[rondon]

This works:

strSQL = "Select 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] & "'"

Hello June 7

Is it possible to add a SELECT statement to limit duplicate records appearing

 

[June7]

Did you try DISTINCT?

 

[rondon]

Did you try DISTINCT?

Yes but not sure I was putting it in the right place as I couldn't get it to work

 

[theDBguy]

Yes but not sure I was putting it in the right place as I couldn't get it to work

Did you see my last post?

 

[rondon]

Did you see my last post?

Yes thankyou it is working now, I am just trying to work out why it is showing a comer at the start, the code line

 If strtext = " " Then
         strtext = rs!AgencyName
         Else
         strtext = strtext & ", " & rs!AgencyName
     End If

Should prevent this was my understanding however it is still happening

 

[theDBguy]

Yes thankyou it is working now, I am just trying to work out why it is showing a comer at the start, the code line

 If strtext = " " Then
         strtext = rs!AgencyName
         Else
         strtext = strtext & ", " & rs!AgencyName
     End If

Should prevent this was my understanding however it is still happening

View attachment 83590

Hi. Does my demo show a comma at the beginning too? I didn't expect that.

 

[rondon]

Hi. Does my demo show a comma at the beginning too? I didn't expect that.

Yes both examples did

 

[Gasman]

When you start, the string is not going to be a space, it will be empty, so test for "" not " "
Some people put the comma after the value and then just remove the last character at the end. You could do it just as easy from the front.?

However if you produce the correct test, you should not need to?

 

[theDBguy]

Yes both examples did

Hi. I downloaded the file I posted and earlier, and I don't see a leading comma in the result.

Can you post an updated copy of your db showing how you implemented my suggestion to help figure out where the extra comma is coming from? Thanks.

 

[rondon]

Hi. I downloaded the file I posted and earlier, and I don't see a leading comma in the result.
View attachment 83598
Can you post an updated copy of your db showing how you implemented my suggestion to help figure out where the extra comma is coming from? Thanks.

Hi DBguy I was not clear sorry the comma appears ok in the query it is when I set a field with the control source to the Module then the comma appears

 

[arnelgp]

here is you Recommendations based on fConcat() function.

 

[rondon]

here is you Recommendations based on fConcat() function.

Thank you this has worked, and thank you to everyone else who contributed to this thread