从一个表中的一个列中的一个列字段到另一个表中的一个,逗号界的值
可以为访问提供的任何帮助,而VB NOOB将不胜感激。我要做的是将值从一个表中加入,并将其插入逗号界的值中的值中的一个表格中的一个字段。我正在尝试将所有说明Linux框的服务器名称列入不同的字段。
表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。
现在,我尝试查看Condenate/Cocece帖子,但是访问中的SQL视图不喜欢声明语句。我的VB技能很糟糕,我似乎无法将代码在VB中用于应用程序。不幸的是,我无法将此数据库转换为我们的SQL Farm,因为我目前还没有服务器来托管它。
谁能向我指向正确的方向?
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.
Table A looks like this
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
Table B has the field I want to insert into: Affected_Machine_Names.
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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您可以使用来自相关记录的串联值艾伦·布朗(Allen Browne)为此。从该网页复制功能代码,并将其粘贴到新的标准模块中。保存模块,并给模块一个名称与函数名称不同; MODCATRAID将起作用。
然后,我认为即使您不熟悉VBA,您也应该能够在查询中使用该功能。
第一次注意,我更改了tablea中的字段名称,用下划线替换空格。通过此更改,此查询...
...产生此结果集:
如果您不能像我一样重命名字段,请使用单独的查询选择不同的操作系统。
将其另存为Qrydistinctoperatingsystems,然后在此版本的主要查询中使用它:
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.
Then I think you should be able to use the function in a query even though you're not proficient with VBA.
First notice I changed the field names in TableA to replace spaces with underscores. With that change, this query ...
... produces this result set:
If you can't rename the fields as I did, use a separate query to select the distinct operating systems.
Save that as qryDistinctOperatingSystems, then use it in this version of the main query:
这是一个相当基本的VBA函数,它将循环遍历列中的每个行,并将其连接到逗号限制的结果字符串。即,就您的示例而言,它将返回“ server01,server02,server03,server04,server05”。 (不要忘记替换列和表名称)
要使用此,
1。
contcolumn(“ Windows”)
将返回“ server04,server03”2。
contcolumn(“ linux”)
将返回“ server01,server02”3。
contcolumn(“ Solaris”)
将返回“ server05”4。
contcolumn(“”)
将返回“”。This is a fairly basic VBA function that will loop through every row in a column, and concatenate it to a comma-delimited result string. i.e., for your example, it will return "Server01, Server02, Server03, Server04, Server05". (Don't forget to replace the column and table names)
To use this,
1.
ConcatColumn("Windows")
will return "Server04, Server03"2.
ConcatColumn("Linux")
will return "Server01, Server02"3.
ConcatColumn("Solaris")
will return "Server05"4.
ConcatColumn("")
will return "".