从串联在一个表中一列字段到另一个表中的一个，逗号分隔值 [英] Concatenate fields from one column in one table into a single, comma delimited value in another table

问题描述

任何可以被提供给访问和VB菜鸟帮助将是很大的AP preciated。我正在试图做的是从一个表串联的值，并将其插入一个逗号分隔值插入另一个表中的字段。我试图把所有的服务器名称是说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.

现在，我试图寻找通过并置/合并的职位，但在访问SQL视图不喜欢的声明语句。我的VB技能吸得很糟糕，我似乎无法得到code在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?

推荐答案

您可以使用并置的相关记录值通过艾伦·布朗这一点。从网页复制功能code，并将其粘贴到一个新的标准模块。保存模块，并给该模块的功能名称不同的名称; 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.

首先声明我在表A改变字段名称，以取代用下划线空间。随着这一变化，该查询...

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屋！