MS Access VBA：将查询结果转换为单个字符串 [英] MS Access VBA: turn query results into a single string

问题描述

我在MS Access中有一个由项目组成的表格，还有另一个表格，销售人员将这些项目绑定在一起。因此，例如铅笔项目可能有4个人将其出售。一旦一切都出现在查询中，我就会得到这样的东西：

I have a table in MS Access composed of items, and there's another table where salespeople tie into those items. So for example the item "pencil" could have 4 people that sold it associated. Once everything comes out of the query I have something like this:

item    seller
pencil  joe
pencil  bob
pencil  charles
pen     john
eraser  ruth
paper   charles

我有一个报告表，我想在其中单行列出每个项目的卖方。有点像：

I have a report form where I'd like to list each item's sellers on a single line. Kind of like:

pencil: bob, joe, charles
pen:    john
eraser: ruth
paper: charles

我认为我可以提出一个创建ADO Recordset对象的解决方案从按项目名称过滤的查询语句中提取，然后在每个循环中使用a遍历记录，并将名称输入每个项目的连接字符串中。听起来这很漫长。有人有更好的方法吗？

I figure I can pound out a solution where I create an ADO Recordset object from a query statement filtered by item name, and then use a for each cycle to go through the records and feed the names into a concatenate string for each item. Sounds like the long way to do it though. Anyone have a better way of doing it?

推荐答案

您可以使用Allen Browne的 ConcatRelated 函数，将每个商品的卖方名称连接到一个单独的字段中。然后在报表的记录源中使用查询。

You can create a query using Allen Browne's ConcatRelated function to concatenate the seller names for each item into a separate field. And then use the query in your report's Record Source.

这是下面查询的输出，其中示例数据存储在名为 YourTable ：

Here is the output from the query below with your sample data stored in a table named YourTable:

item    sellers
------  ------------------
eraser  ruth
paper   charles
pen     john
pencil  joe, bob, charles

SELECT
    y.item,
    ConcatRelated
        (
            "seller",
            "YourTable",
            "[item]='" & [y].[item] & "'"
        ) AS sellers
FROM YourTable AS y
GROUP BY y.item;

这篇关于MS Access VBA：将查询结果转换为单个字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！