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

问题描述

可能重复:
女士访问查询:通过查询将行合并

Possible Duplicate:
Ms Access Query: Concatenating Rows through a query

我有一个表，该表有很多列，但有两个有趣的地方:订单号和产品类型.当前，该表的每个订单具有多种产品类型.如果客户订购了电话服务，电视服务和Internet服务，则将有3条记录-每个记录一个，但所有记录具有相同的订单号.我想创建一个引用表来存储一个由客户订购的所有服务组成的字符串.这样，我可以使用这种更逻辑的方法来汇总我的数据.我正在使用标准的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;

我在Access 2007中测试了该查询，并将您的示例数据保存在名为" YourTable "的表中.它返回了您要求的结果.但是，这仅在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屋！