3

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 all of our incidents, as well as a hub to open the investigation's associated form to be used by the investigator. I developed three regular tables and a junction table:

  • Investigations - General Information
  • Target(s)
  • Victim(s)
  • Target/Victim Joiner

The General Info Table has a one to many relationship to Targets, and Targets and Victims have a many to many relationship connected through the joiner table. This joiner table is also where the allegations and outcomes are so that each target and victim are have their own associated charge and outcome.

I ran two queries; one named Tar/VicQuery that features information gathered from the related tables above:

InternalIncidentID  TargetID    TargetFullName  VictimID    VictimFullName  Offense(s)  Outcome(s)
JAW-1               TAR-1       Smith Davie     VIC-1       Harris Michelle Neglect    Substantiated
JAW-1               TAR-1       Smith Davie     VIC-2       Jones Mary      Neglect    Substantiated
JAW-2               TAR-2       Thimble John    VIC-3       Man Joseph      Abuse      Substantiated
JAW-2               TAR-3       Rocket Sammy    VIC-3       Man Joseph      Abuse      Substantiated
JAW-2               TAR-4       Berkowitz Josh  VIC-3       Man Joseph      Abuse      Substantiated
JAW-3               TAR-5       McGowen Melissa VIC-4       Root James      Theft      Founded
JAW-3               TAR-5       McGowen Melissa VIC-5       Lopez Randy     Theft      Founded
JAW-3               TAR-5       McGowen Melissa VIC-6       Martino Bruno   Theft      Founded
JAW-3               TAR-6       Thimble John    VIC-4       Root James      Theft      Unfounded
JAW-3               TAR-6       Thimble John    VIC-5       Lopez Randy     Theft      Unfounded
JAW-3               TAR-6       Thimble John    VIC-6       Martino Bruno   Theft      Founded

And another Query that runs off the above mentioned Query called TargetQuery:

InternalIncidentID  TargetName
JAW-1               Smith Davie, Smith Davie
JAW-2               Thimble John, Rocket Sammy, Berkowitz Josh
JAW-3               McGowen Melissa, McGowen Melissa, McGowen Melissa, Thimble John, Thimble John, Thimble John

This above query uses Allen Browne's method of ConcatRelated to combine rows of data that have the same incident ID and concatenate the targets of the investigation. I have followed the instructions that Allen expresses on this page including creating the associated module, pasting in his function, and then attempting to utilize it as part of a query. I am also looking to do the same in another (or the same, if it's possible) query for victims attached to a case.

SQL Code for TargetQuery: 

SELECT DISTINCT [Tar/Vic Query].InternalIncidentID, ConcatRelated("TargetFullName","[Tar/Vic Query]","InternalIncidentID= " & [Tar/Vic Query].[InternalIncidentID]) AS TargetName
FROM [Tar/Vic Query];

The results, as seen above, are very close to what I am hoping to achieve. The ideal would be that duplicate names do not appear again as part of the concatenate. How do I make this happen?

I attempted to use unique values to remedy this, which helped considerably in one way (reducing the number of records from 11 to 3), but did not solve the issue of "Davie Smith" appearing multiple times in the field for a case (in this case, he had two victims, as expressed in the first table). This is only part one of my conundrum unfortunately as I am fairly new to Access and SQL in general; I am hoping that, by the end of this, my form will look like this (with more fields prior to target and victim names but you get the idea):

  InternalIncidentID    TargetName                                 VictimName 
    JAW-1               Smith Davie                                Harris Michelle, Jones Mary
    JAW-2               Thimble John, Rocket Sammy, Berkowitz Josh Man Joseph
    JAW-3               McGowen Melissa Thimble John               Root James, Lopez Randy, Martino Bruno

Any help/education that can be provided on this would be much appreciated. I am well aware of the gap in my understanding of this program currently and am appreciative of all patience that is provided to me regarding this question. Below are the threads here on this website and elsewhere that I have reviewed prior to making this question:

Source 1 Source 2 Source 3 Source 4 Source 5

June7
  • 19,874
  • 8
  • 24
  • 34
Chris F
  • 59
  • 7
  • I apologize if I wasn't clear; I am looking to have the field in the second table above that says something like "Smith Davie, Smith Davie" to just say "Smith Davie" if he is the only target. Likewise, I want to feature all the targets in that one field concatenated from the first table's arrangement. I have also used DISTINCT in the above SQL query code. – Chris F Dec 13 '19 at 21:34
  • InternalIncidentID is text, so you need more quotation marks: **"[InternalIncidentID]="""& [Tar/Vic Query].[InternalIncidentID] & """"** – Rene Dec 13 '19 at 21:48
  • Are the ID fields really text type or are you actually saving autonumber ID into those fields and displaying associated descriptive value? In other words, what are the PK/FK linking fields? Advise not to use spaces nor punctuation/special characters in naming convention. – June7 Dec 14 '19 at 01:20
  • The PK in each table is an autonumber with a format that places either JAW, TAR, or VIC and a "-" prior to the number being sequenced. The Internal Incident ID is the PK for General info table and the foreign key for the Target table, the TargetID is the PK for the Target Table, and the VictimID is the PK for the Victim Table. The latter two connect on a junction table that has both the TargetID and VictimID as the PKs to create the many to many relationship. – Chris F Dec 14 '19 at 05:02
  • So InternalIncidentID is really a number field with formatting to display with prefix? – June7 Dec 14 '19 at 10:25
  • Yes; it is an autonumbered, incremental ID with the format "JAW-"# to create the sequence. the TargetID (TAR-#) and VictimID (Vic-#) work the same way. – Chris F Dec 19 '19 at 15:17

2 Answers2

4

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

June7
  • 19,874
  • 8
  • 24
  • 34
  • I apologize that I have not gotten back to you sooner; work and my newborn have kept me away. Your codes above did the trick once I removed the inner (') on the concatenate query. I utilized the concept multiple times and managed the results I was looking for. Thank you so much for your help! – Chris F Dec 19 '19 at 15:20
0

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
End If

TO:

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

Leaving here incase this helps someone in the future!

JD1
  • 27
  • 5