从一个表中的一个列中的一个列字段到另一个表中的一个,逗号界的值

发布于 2025-02-09 09:52:28 字数 578 浏览 2 评论 0原文

可以为访问提供的任何帮助,而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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

风筝有风,海豚有海 2025-02-16 09:52:28

您可以使用来自相关记录的串联值艾伦·布朗(Allen Browne)为此。从该网页复制功能代码,并将其粘贴到新的标准模块中。保存模块,并给模块一个名称与函数名称不同; MODCATRAID将起作用。

然后,我认为即使您不熟悉VBA,您也应该能够在查询中使用该功能。

第一次注意,我更改了tablea中的字段名称,用下划线替换空格。通过此更改,此查询...

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

...产生此结果集:

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

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

SELECT DISTINCT TableA.[Operating System]
FROM TableA;

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

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

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 ...

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;

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;
为你鎻心 2025-02-16 09:52:28

这是一个相当基本的VBA函数,它将循环遍历列中的每个行,并将其连接到逗号限制的结果字符串。即,就您的示例而言,它将返回“ server01,server02,server03,server04,server05”。 (不要忘记替换列和表名称)

Function ConcatColumn(OS As String) As String
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("Select * from TableA")

    Dim result As String

    'For every row after the first, add a comma and the field value:
    While rst.EOF = False
        If rst.Fields("Operating System") = OS Then _
            result = result & ", " & rst.Fields("MyValue")
        rst.MoveNext
    Wend

    'Clean it up a little and put out the result
    If Left(result, 2) = ", " Then result = Right(result, Len(result) - 2)
    Debug.Print result
    ConcatColumn = result
End Function

要使用此,
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)

Function ConcatColumn(OS As String) As String
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("Select * from TableA")

    Dim result As String

    'For every row after the first, add a comma and the field value:
    While rst.EOF = False
        If rst.Fields("Operating System") = OS Then _
            result = result & ", " & rst.Fields("MyValue")
        rst.MoveNext
    Wend

    'Clean it up a little and put out the result
    If Left(result, 2) = ", " Then result = Right(result, Len(result) - 2)
    Debug.Print result
    ConcatColumn = result
End Function

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 "".

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文