将一个表中一列的字段连接到另一个表中的单个逗号分隔值 [英] Concatenate fields from one column in one table into a single, comma delimited value in another table

问题描述

任何可以提供给 Access 和 VB 菜鸟的帮助将不胜感激.我想要做的是连接一个表中的值，并将其作为逗号分隔的值插入到另一个表中的字段中.我正在尝试将所有服务器名称(例如 Linux 机器)连接到不同的字段中.

Any help that can be provided to a Access and VB noob would be greatly appreciated. What I'm trying to do is concatenate the values from one table and insert it as a comma delimited value into a field in another table. I'm trying to take all the server names that are say Linux boxes and concatenate them into a different field.

表 A 看起来像这样

Machine Name | Zone   | Operating System
----------------------------------------
Server01      Zone A    Linux
Server02      Zone B    Linux
Server03      Zone A    Windows
Server04      Zone C    Windows
Server05      Zone B    Solaris

表 B 有我想插入的字段:Affected_Machine_Names.

Table B has the field I want to insert into: Affected_Machine_Names.

现在，我已尝试浏览 Concatenate/Coalesce 帖子，但 Access 中的 SQL 视图不喜欢 Declare 语句.我的 VB 技能很烂，而且我似乎无法让代码在 VB 应用程序中工作.不幸的是，我无法将此数据库转换为我们的 SQL 场，因为我目前没有可用的服务器来托管它.

Now, I've tried looking through the Concatenate/Coalesce posts, but the SQL view in Access doesn't like the Declare statements. My VB skills suck badly and I can't seem to get the code to work in VB for Applications. Unfortunately, I can't get this database converted into our SQL farm cause I don't have a server available at the moment to host it.

谁能指出我正确的方向?

Can anyone point me in the right direction?

推荐答案

您可以使用 连接来自相关记录的值 艾伦布朗为此.从该网页复制功能代码并将其粘贴到新的标准模块中.保存模块并给模块一个不同于函数名的名称；modConcatRelated 会起作用.

You can use Concatenate values from related records by Allen Browne for this. Copy the function code from that web page and paste it into a new standard module. Save the module and give the module a name different from the function name; modConcatRelated would work.

那么我认为即使您不精通 VBA，您也应该能够在查询中使用该函数.

Then I think you should be able to use the function in a query even though you're not proficient with VBA.

首先注意到我更改了 TableA 中的字段名称以用下划线替换空格.随着这一变化，这个查询...

First notice I changed the field names in TableA to replace spaces with underscores. With that change, this query ...

SELECT
    sub.Operating_System, 
    ConcatRelated("Machine_Name", "TableA", 
        "Operating_System = '" & sub.Operating_System & "'") AS Machines
FROM [SELECT DISTINCT Operating_System FROM TableA]. AS sub;

... 产生这个结果集:

... produces this result set:

Operating_System Machines
Linux            Server01, Server02
Solaris          Server05
Windows          Server03, Server04

如果您不能像我一样重命名字段，请使用单独的查询来选择不同的操作系统.

If you can't rename the fields as I did, use a separate query to select the distinct operating systems.

SELECT DISTINCT TableA.[Operating System]
FROM TableA;

将其另存为 qryDistinctOperatingSystems，然后在此版本的主查询中使用它:

Save that as qryDistinctOperatingSystems, then use it in this version of the main query:

SELECT
    sub.[Operating System], 
    ConcatRelated("[Machine Name]", "TableA", 
        "[Operating System] = '" & sub.[Operating System] & "'") AS Machines
FROM qryDistinctOperatingSystems AS sub;

这篇关于将一个表中一列的字段连接到另一个表中的单个逗号分隔值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！