在 Access 中的一个字段中连接多行? [英] Concatenate multiple rows in one field in Access?

问题描述

可能的重复:
Ms Access 查询:通过查询连接行

我有一个包含许多列的表，但有两个感兴趣的列:订单号和产品类型.目前，该表每个订单有多个产品类型.如果客户订购了电话服务、电视服务和互联网服务，那么将有三条记录——每项服务一个，但都具有相同的订单号.我想创建一个参考表来存储一个包含客户订购的所有服务的串联字符串.这样我就可以使用这种更合乎逻辑的方法来总结我的数据.我使用的是标准 Access 2010 数据库.

I have a table that has many columns, but two of interest: Order Number and Product Type. Currently the table has multiple Product Types per Order. If the customer ordered phone service, TV service and Internet service, then there would be three records - one for each service but all having the same order number. I want to create a reference table to store a concatenated string with all of the services the customer ordered. This way I can summarize my data using this more logical method. I'm using a standard Access 2010 database.

**Current table:**

Order Number | Product Types

100001 | TV

100001 | Phone

100001 | Internet

100002 | Phone

100003 | TV

100003 | Internet

所需参考表

100001 | TV/Phone/Internet

100002 | Phone

100003 | TV/Internet

推荐答案

Allen Browne 提供了一个您可能会发现对此有用的函数:连接相关记录中的值.将该函数的代码保存在标准模块中.

Allen Browne provides a function which you may find useful for this: Concatenate values from related records. Save that function's code in a standard module.

SELECT DISTINCT
    [Order Number],
    ConcatRelated("[Product Types]",
        "YourTable",
        "[Order Number] = " & [Order Number],
        "[Product Types]",
        "/"
        ) AS All_Product_Types
FROM YourTable;

我使用保存在名为YourTable"的表中的示例数据在 Access 2007 中测试了该查询.它返回了您要求的结果.但是，这仅适用于 Access 会话中.如果您想从 Access 外部(如从 ASP)运行此查询，则用户定义的函数不可用，因此您会收到关于 ConcatRelated() 未识别的错误.

I tested that query in Access 2007 with your sample data saved in a table named "YourTable". It returned the results you asked for. However, this only works from within an Access session. If you wanted to run this query from outside Access (like from ASP), user-defined functions are not available, so you would get an error about ConcatRelated() not recognized.

因此，您可以在需要时使用查询来检索连接的值.但是，如果您存储这些连接的值，它们很快就会与基表数据的更改不同步.

So you can use a query to retrieve the concatenated values whenever you need them. However if you store those concatenated values, they can quickly be out of sync with changes to the base table's data.

这篇关于在 Access 中的一个字段中连接多行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！