Concatenate fields from one column in one table into a single, comma delimited value in another table

Question

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?

Reiterate

This can be done with a User Defined Function, but are you sure you wish to do this? What is the real world problem? Someone may be able to suggest a lateral approach more in keeping with a relational database. – Convolve

Oddly enough, this is easier with ADO, for example https://mcmap.net/q/1009162/-combine-rows-concatenate-rows/… – Convolve

@Waughsie: You can use my DJoin function. – Pavel

Answer 1

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;

Bornite

I got it working with this! I actually had the field names with underscores, but when I copied the table, I copied the label IDs that I put in without the underscores, so your first code snippet worked. I appreciate the help! – Reiterate

Answer 2

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

Yadirayaeger

Right, but what if I just separated all the operating systems into 3 separate tables from my master table in another query. It's ugly, but it just might work. I'll try it and report back. Thanks! – Reiterate

In @PowerUser's defence, the requirement was not described very clearly by the OP! – Villanelle

Thanks for the reminder, HansUp. It's simple enough to fix. I've added some IF statements and put in a OS parameter. Better? – Yadirayaeger