从串联在一个表中一列字段到另一个表中的一个,逗号分隔值 [英] 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屋!