ms access - 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?

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;