Concatrelated() Function To Provide Unique Values On A Form

I have started developing an Access Database for my agency to utilize, starting with incident management. I have been attempting to develop a form that acts as a master index for a

Solution 1:

Allen's procedure allows only to provide WHERE criteria to the function. Other versions I've seen allow to pass an entire SQL statement.

Will have to build 2 queries that return DISTINCT values for each InternalIncidentID - one for targets and one for victims. Those queries will be source for each of the calls to Allen's function.

qryIncTargets

SELECT DISTINCT InternalIncidentID, TargetFullName FROM [Tar/Vic Query];

qryIncVictims

SELECT DISTINCT InternalIncidentID, VictimFullName FROM [Tar/Vic Query];

qryConcatenate

SELECT Investigations.InternalIncidentID, 
ConcatRelated("TargetFullName","qryIncTargets","InternalIncidentID='" & [InternalIncidentID] & "'") AS Tars, 
ConcatRelated("VictimFullName","qryIncVictims","InternalIncidentID='" & [InternalIncidentID] & "'") AS Vics
FROM Investigations;

Could eliminate [Tar/Vic Query] and instead build the two DISTINCT queries with JOIN of [Target/Victim Joiner] to [Target(s)] and [Victim(s)].

Solution 2:

This post is somewhat old, but just came across it trying to do something similar (or maybe the exact same actually..). I'm pulling from one table, and using Allen Browne's method of ConcatRelated to roll-up to a single row per ID with values comma delimited.

I found that one simple mod to the ConcatRelated function will eliminate values being repeated in the comma delimited roll-up.

FROM:

'Build SQL string, and get the records.
strSql = "SELECT " & strField & " FROM " & strTable
If strWhere <> vbNullString Then
    strSql = strSql & " WHERE " & strWhere
EndIf

TO:

'Build SQL string, and get the records.
strSql = "SELECT DISTINCT " & strField & " FROM " & strTable
If strWhere <> vbNullString Then
    strSql = strSql & " WHERE " & strWhere
EndIf

Leaving here incase this helps someone in the future!