ConcatRelated()函数可在表单上提供唯一值 [英] 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 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:
- 调查-一般信息
- 目标
- 受害者
- 目标/受害者加入者
常规信息表与目标具有一对多关系，目标和受害人具有通过联接器表连接的多对多关系.该表也可以用于指控和结果，以便每个目标和受害者都有各自相关的指控和结果.
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.
我跑了两个查询；一种名为Tar/VicQuery的功能，具有从以上相关表中收集的信息:
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
另一个运行上述查询的查询称为TargetQuery:
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
此查询使用Allen Browne的ConcatRelated方法来组合具有相同事件ID的数据行，并串联调查目标.我已按照艾伦在此页面上表示的说明 进行操作，包括创建相关模块，并将其粘贴到函数，然后尝试将其用作查询的一部分.我还希望在另一个(或相同，如果可能的话)查询中对与案件相关的受害者进行相同的查询.
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.
TargetQuery的SQL代码:
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?
我尝试使用唯一值来对此进行补救，这在一种方式上有很大帮助(将记录的数量从11条减少到3条)，但是并没有解决"Davie Smith"在该领域多次出现的问题.案(在本案中，他有两名受害者，如第一张表所示).不幸的是，这只是我的难题之一，因为我一般对Access和SQL还不熟悉.我希望到此结束时，我的表单将如下所示(在目标名称和受害者名称之前有更多字段，但您会明白的):
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:
推荐答案
Allen的过程仅允许向函数提供WHERE条件.我见过的其他版本允许传递整个SQL语句.
Allen's procedure allows only to provide WHERE criteria to the function. Other versions I've seen allow to pass an entire SQL statement.
将必须构建2个查询，为每个InternalIncidentID返回DISTINCT值-一个用于目标，一个用于受害者.这些查询将作为对Allen函数的每个调用的来源.
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
qryIncTargets
SELECT DISTINCT InternalIncidentID, TargetFullName FROM [Tar/Vic Query];
qryIncVictims
qryIncVictims
SELECT DISTINCT InternalIncidentID, VictimFullName FROM [Tar/Vic Query];
qryConcatenate
qryConcatenate
SELECT Investigations.InternalIncidentID,
ConcatRelated("TargetFullName","qryIncTargets","InternalIncidentID='" & [InternalIncidentID] & "'") AS Tars,
ConcatRelated("VictimFullName","qryIncVictims","InternalIncidentID='" & [InternalIncidentID] & "'") AS Vics
FROM Investigations;
可以消除[Tar/Vic查询]，而是使用JOIN将[Target/Victim Joiner]与[Target(s)]和[Victim(s)]建立两个DISTINCT查询.
Could eliminate [Tar/Vic Query] and instead build the two DISTINCT queries with JOIN of [Target/Victim Joiner] to [Target(s)] and [Victim(s)].
这篇关于ConcatRelated()函数可在表单上提供唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
