Solved Concatenate values from related records

[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