在MS Access中将多行串联为一行 [英] Concatenating multiple rows into single line in MS Access

问题描述

可能重复:
在Access 2007中合并行
Access 2007-并置一张表中的一列将字段转换成另一张表中的单个逗号分隔的值

Possible Duplicate:
Combine rows in Access 2007
Access 2007 - Concatenate fields from one column in one table into a single, comma delmited value in another table

当前，我有一个类似于以下的表结构:

Currently I have a table structure that is somewhat like this:

名称 --- 猫 --- 描述 --- Thresh --- Perc --- Err --- BP
鲍勃------- C1 ------- Inf -------- 7Per -------- 0.05 ------ 0 ----- ADC2
鲍勃------- C1 ------- Inf -------- 7Per -------- 0.05 ------ 2 ----- BAC2
鲍勃------- C1 ------- Inf -------- 7Per -------- 0.05 ------ 0 ----- RBE2
鲍勃------- C1 ------- Inf -------- 7Per -------- 0.05 ------ 8 ----- VBE2
鲍勃------- C1 ------- Inf -------- 7Per -------- 0.05 ------ 6 ----- AEC2
鲍勃------- C1 ------- Inf -------- 7Per -------- 0.05 ------ 0 ----- PBC2
鲍勃------- C2 ------- Com ------ 8Per -------- 0.45 ------ 1 ----- XBC4
鲍勃------- C2 ------- Com ------ 8Per -------- 0.45 ------ 0 ----- AEC2
鲍勃------- C2 ------- Com ------ 8Per -------- 0.45 ------ 0 ----- PBC2
鲍勃------- C2 ------- Com ------ 8Per -------- 0.45 ------ 3 ----- ADC2
鲍勃------- C2 ------- Com ------ 8Per -------- 0.45 ------ 0 ----- ADC2
鲍勃------- C2 ------- Com ------ 8Per -------- 0.45 ------ 0 ----- BAC2
乔-------- C1 ------- Inf --------- 7Per -------- 0.05 ------ 0 ----- PBC2
乔-------- C1 ------- Inf --------- 7Per -------- 0.05 ------ 0 ----- ZTM2
乔-------- C1 ------- Inf --------- 7Per -------- 0.05 ------ 2 ----- QYC2
乔-------- C1 ------- Inf --------- 7Per -------- 0.05 ------ 0 ----- FLC2
乔-------- C1 ------- Inf --------- 7Per -------- 0.05 ------ 1 ----- KSC2
乔-------- C1 ------- Inf --------- 7Per -------- 0.05 ------ 0 ----- JYC2

Name --- Cat --- Desc --- Thresh --- Perc --- Err --- BP
Bob -------C1-------Inf--------7Per--------0.05------0-----ADC2
Bob -------C1-------Inf--------7Per--------0.05------2-----BAC2
Bob -------C1-------Inf--------7Per--------0.05------0-----RBE2
Bob -------C1-------Inf--------7Per--------0.05------8-----VBE2
Bob -------C1-------Inf--------7Per--------0.05------6-----AEC2
Bob -------C1-------Inf--------7Per--------0.05------0-----PBC2
Bob -------C2-------Com------8Per--------0.45------1-----XBC4
Bob -------C2-------Com------8Per--------0.45------0-----AEC2
Bob -------C2-------Com------8Per--------0.45------0-----PBC2
Bob -------C2-------Com------8Per--------0.45------3-----ADC2
Bob -------C2-------Com------8Per--------0.45------0-----ADC2
Bob -------C2-------Com------8Per--------0.45------0-----BAC2
Joe--------C1-------Inf---------7Per--------0.05------0-----PBC2
Joe--------C1-------Inf---------7Per--------0.05------0-----ZTM2
Joe--------C1-------Inf---------7Per--------0.05------2-----QYC2
Joe--------C1-------Inf---------7Per--------0.05------0-----FLC2
Joe--------C1-------Inf---------7Per--------0.05------1-----KSC2
Joe--------C1-------Inf---------7Per--------0.05------0-----JYC2

我要执行的操作是每个名称"和每个猫"都有1行，这将汇总所有"Err"(每个名称"和猫")并仅连接"BP"字段成一行.如:

What i'm looking to do is have 1 line per "Name" and per "Cat", that will sum up all the "Err" (per "Name" and "Cat") and concatenate only the "BP" fields into a single line. Such as:

名称 --- Cat --- Desc --- Thresh --- Perc --- Err --- BP
鲍勃------- C1 ------- Inf -------- 7Per -------- 0.05 ----- 16 ----- BAC2，VBE2， AEC2
鲍勃------- C2 ------ Com ------ 8Per -------- 0.45 ------ 4 ------ XBC4，ADC2
乔-------- C1 ------- Inf -------- 7Per -------- 0.05 ------ 3 ------ QYC2 ，KSC2

Name --- Cat --- Desc --- Thresh --- Perc --- Err --- BP
Bob -------C1-------Inf--------7Per--------0.05-----16-----BAC2, VBE2, AEC2
Bob -------C2------Com------8Per--------0.45------4------XBC4, ADC2
Joe--------C1-------Inf--------7Per--------0.05------3------QYC2, KSC2

有人问过类似的问题，但是由于我对VBA脚本的了解是初学者，所以我似乎无法应用它.有什么办法可以通过SQL来完成所有这些工作吗?如果VBA脚本是唯一的选择(即创建函数)，则将不胜感激.预先谢谢你.

There have been similar questions asked but I cannot seem to apply it as my knowledge of VBA scripting is beginner. Is there any way to do all of this via SQL? If VBA scripting is the only option (ie. creating a function), any help would be greatly appreciated. Thank You in advance.

问题2:
我根据Allen Browne的指南创建了函数.该模块另存为modConcatRelated.现在，我尝试运行此查询(我不确定这是否是获取我正在寻找的结果的正确SQL):

Question part 2:
I created the function as per Allen Browne's guide. The module is saved as modConcatRelated. Now, i've tried to run this query (im not sure if this is the correct SQL to get the result that i'm looking for):

SELECT
    [Name],
    [Cat],
    [Desc],
    [Thresh],
    [Perc],
    sum([Err]),
    ConcatRelated("[BP]", "make_table_bp", "[Err] = " & [BP])
FROM make_table_bp
GROUP BY
    [Name],
    [Cat],
    [Desc],
    [Thresh],
    [Perc],
    [Err],
    [BP];  

它显示错误3061.参数太少.预期为1."还说未定义函数ConcatRelated".我正在寻找有关如何创建正确的SQL语句的指南，以便可以正确调用ConcatRelated函数并产生如上所述的结果.再次感谢.

It said "Error 3061. Too few parameters. Expected 1." Also it said "Undefined Function ConcatRelated." I'm looking for guidance on how to create the correct SQL statement so that I can call the ConcatRelated function correctly and yield the result as depicted above. Thanks again.

下一个问题:
如果表格中的唯一日期字段标记为表格的最后一列，该怎么办.像这样:

Next question:
What if the table had a unique date field tagged on as the last column in the table. Something like this:

名称 --- 猫 --- 描述 --- Thresh --- Perc --- Err --- BP --- Date

Name --- Cat --- Desc --- Thresh --- Perc --- Err --- BP --- Date

鲍勃------- C1 ------- Inf -------- 7Per -------- 0.05 ------ 0 ----- ADC2--12/02/2011
鲍勃------- C1 ------- Inf -------- 7Per -------- 0.05 ------ 2 ----- BAC2-- 2011年9月5日
鲍勃------- C1 ------- Inf -------- 7Per -------- 0.05 ------ 0 ----- RBE2- 11/02/2011
鲍勃------- C1 ------- Inf -------- 7Per -------- 0.05 ------ 8 ----- VBE2-- 2012年8月14日
鲍勃------- C1 ------- Inf -------- 7Per -------- 0.05 ------ 6 ----- AEC2-- 2009年2月25日
鲍勃------- C1 ------- Inf -------- 7Per -------- 0.05 ------ 0 ----- PBC2-- 2011年7月2日
鲍勃------- C2 ------- Com ------ 8Per -------- 0.45 ------ 1 ----- XBC4--09/05/2011
鲍勃------- C2 ------- Com ------ 8Per -------- 0.45 ------ 0 ----- AEC2--02/02/2010
鲍勃------- C2 ------- Com ------ 8Per -------- 0.45 ------ 0 ----- PBC2--08/14/2012
鲍勃------- C2 ------- Com ------ 8Per -------- 0.45 ------ 3 ----- ADC2--05/05/2001
鲍勃------- C2 ------- Com ------ 8Per -------- 0.45 ------ 0 ----- ADC2--08/02/2010
鲍勃------- C2 ------- Com ------ 8Per -------- 0.45 ------ 0 ----- BAC2--06/17/2010
乔-------- C1 ------- Inf --------- 7Per -------- 0.05 ------ 0 ----- PBC2 --08/14/2012
乔-------- C1 ------- Inf --------- 7Per -------- 0.05 ------ 0 ----- ZTM2 --09/05/2011
Joe -------- C1 ------- Inf --------- 7Per -------- 0.05 ------ 2 ----- QYC2 --05/17/2010
乔-------- C1 ------- Inf --------- 7Per -------- 0.05 ------ 0 ----- FLC2 2010年3月19日
乔-------- C1 ------- Inf --------- 7Per -------- 0.05 ------ 1 ----- KSC2 --09/05/2011
乔-------- C1 ------- Inf --------- 7Per -------- 0.05 ------ 0 ----- JYC2 --08/14/2012

Bob -------C1-------Inf--------7Per--------0.05------0-----ADC2--12/02/2011
Bob -------C1-------Inf--------7Per--------0.05------2-----BAC2--09/05/2011
Bob -------C1-------Inf--------7Per--------0.05------0-----RBE2--11/02/2011
Bob -------C1-------Inf--------7Per--------0.05------8-----VBE2--08/14/2012
Bob -------C1-------Inf--------7Per--------0.05------6-----AEC2--02/25/2009
Bob -------C1-------Inf--------7Per--------0.05------0-----PBC2--07/02/2011
Bob -------C2-------Com------8Per--------0.45------1-----XBC4--09/05/2011
Bob -------C2-------Com------8Per--------0.45------0-----AEC2--02/02/2010
Bob -------C2-------Com------8Per--------0.45------0-----PBC2--08/14/2012
Bob -------C2-------Com------8Per--------0.45------3-----ADC2--05/05/2001
Bob -------C2-------Com------8Per--------0.45------0-----ADC2--08/02/2010
Bob -------C2-------Com------8Per--------0.45------0-----BAC2--06/17/2010
Joe--------C1-------Inf---------7Per--------0.05------0-----PBC2--08/14/2012
Joe--------C1-------Inf---------7Per--------0.05------0-----ZTM2--09/05/2011
Joe--------C1-------Inf---------7Per--------0.05------2-----QYC2--05/17/2010
Joe--------C1-------Inf---------7Per--------0.05------0-----FLC2--3/19/2010
Joe--------C1-------Inf---------7Per--------0.05------1-----KSC2--09/05/2011
Joe--------C1-------Inf---------7Per--------0.05------0-----JYC2--08/14/2012

假设我想建立一个查询，例如:向我显示所有仍采用相同格式的记录:

Let's say I wanted to build a query to say something like: show me all records still within this same format:

名称 --- 猫 --- 描述 --- Thresh --- Perc --- Err --- BP
鲍勃------- C1 ------- Inf -------- 7Per -------- 0.05 ----- 16 ----- BAC2，VBE2， AEC2
鲍勃------- C2 ------ Com ------ 8Per -------- 0.45 ------ 4 ------ XBC4，ADC2
乔-------- C1 ------- Inf -------- 7Per -------- 0.05 ------ 3 ------ QYC2 ，KSC2

Name --- Cat --- Desc --- Thresh --- Perc --- Err --- BP
Bob -------C1-------Inf--------7Per--------0.05-----16-----BAC2, VBE2, AEC2
Bob -------C2------Com------8Per--------0.45------4------XBC4, ADC2
Joe--------C1-------Inf--------7Per--------0.05------3------QYC2, KSC2

但是日期范围为2009年1月1日至2011年9月31日

But for a date range of 01/01/2009 to 09/31/2011

@HansUp您能帮上忙吗?

@HansUp could you help with this?

推荐答案

我为GROUP BY使用了一个子查询，该子查询计算了每个组的Err之和.然后，我添加了ConcatRelated函数(来自Allen Browne的 )，并带有子查询返回的字段.这是查询和查询的输出(基于make_table_bp中的示例数据):

I used a subquery for the GROUP BY which computes the Sum of Err for each group. Then I added the ConcatRelated function (from Allen Browne) with the fields returned by the subquery. This is the query and the output (based on your sample data in make_table_bp) from the query:

SELECT
    sub.[Name],
    sub.Cat,
    sub.[Desc],
    sub.Thresh,
    sub.Perc,
    sub.SumOfErr,
    ConcatRelated("BP",
        "make_table_bp",
        "[Err] > 0 AND [Name] = '" & sub.[Name]
        & "' AND Cat = '"
        & sub.Cat & "'",
        "BP")
        AS concat_BP
FROM
    (SELECT
        q.[Name],
        q.Cat,
        q.[Desc],
        q.Thresh,
        q.Perc,
        Sum(q.[Err]) AS SumOfErr
    FROM make_table_bp AS q
    GROUP BY
        q.[Name],
        q.Cat,
        q.[Desc],
        q.Thresh,
        q.Perc
    ) AS sub
ORDER BY
    sub.Name,
    sub.Cat;

查询输出以下结果集:

Name Cat Desc Thresh Perc SumOfErr concat_BP
Bob  C1  Inf  7Per   0.05       16 AEC2, BAC2, VBE2
Bob  C2  Com  8Per   0.45        4 ADC2, XBC4
Joe  C1  Inf  7Per   0.05        3 KSC2, QYC2

注意，我在查询中引用的每个位置都用方括号将Name，Desc和Err括起来.所有都是保留字(请参阅 Access中的问题名称和保留字).如果可能，请为这些字段选择其他名称.如果不是，请使用方括号以避免混淆db引擎.

Notice I enclosed Name, Desc, and Err with square brackets every place they were referenced in the query. All are reserved words (see Problem names and reserved words in Access). Choose different names for those fields if possible. If not, use the square brackets to avoid confusing the db engine.

但是，除非/直到您的数据库引擎识别出ConcatRelated函数的副本，否则这将无法工作.我不明白为什么不是这样；我按照您列出的用于存储功能代码的相同步骤操作，在我的系统上可以正常工作.

But this will not work unless/until your copy of the ConcatRelated function is recognized by your data base engine. I don't understand why it's not; I followed the same steps you listed for storing the function code, and this works fine on my system.

编辑:我使用表的版本(该表具有[Err]作为数字数据类型)测试了该查询.听起来您的是文字.在这种情况下，我建议您也将其更改为数字.我看不到将数值存储为文本而不是实际数字的好处.

Edit: I tested that query with my version of the table, which has [Err] as a numeric data type. Sounds like yours is text instead. In that case, I'll suggest you change yours to numeric, too. I don't see the benefit of storing numerical values as text instead of actual numbers.

但是，如果您将[Err]保留为文本，则可以调整查询以对其进行处理.改变这个...

However if you're stuck with [Err] as text, you can adapt the query to deal with it. Change this ...

"[Err] > 0 AND [Name] = '" & sub.[Name]

对此...

"Val([Err]) > 0 AND [Name] = '" & sub.[Name]

当我使用[Err]作为文本数据类型进行测试时，该更改避免了条件表达式中的数据类型不匹配"错误.但是，我也更改了此内容...

That change prevented the "Data type mismatch in criteria expression" error when I tested with [Err] as text data type. However, I also changed this ...

Sum(q.[Err]) AS SumOfErr

对此...

Sum(Val(q.[Err])) AS SumOfErr

AFAICT认为，第二次更改并非绝对必要.当您要求对Sum()进行数字运算时，db引擎似乎愿意接受数字作为文本.但是，我更喜欢将它们显式转换为数值，而不是依靠db引擎代表我做出正确的猜测.数据库引擎还有足够的其他东西要处理，因此我尝试准确地告诉它我想要什么.

AFAICT that second change is not strictly necessary. The db engine seems willing to accept numbers as text when you ask it to Sum() them. However I prefer to explicitly transform them to numerical values rather than depend on the db engine to make the right guess on my behalf. The db engine has enough other stuff to deal with, so I try to tell it exactly what I want.

Edit2 :如果只希望串联唯一值，则可以修改ConcatRelated()函数.找到代码的这一部分...

Edit2: If you want only unique values concatenated, you can modify the ConcatRelated() function. Find this section of the code ...

'Build SQL string, and get the records.
strSql = "SELECT " & strField & " FROM " & strTable

并将其更改为此...

and change it to this ...

'Build SQL string, and get the records.
strSql = "SELECT DISTINCT " & strField & " FROM " & strTable

这篇关于在MS Access中将多行串联为一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！