Combining Data into one Field Using MS Access and SQL

Question

I have these two tables:

Groups:

+----+-------+------+
| ID | Title | Info |
+----+-------+------+
|  1 | Red   |      |
|  2 | Blue  |      |
|  3 | Green |      |
+----+-------+------+

Contacts:

+----+-------+----------------+
| ID | Name  |    Contact     |
+----+-------+----------------+
|  1 | Joe   | joe@test.com   |
|  3 | Billy | billy@test.com |
|  3 | Sally | sally@test.com |
+----+-------+----------------+

I am trying to match them based on ID, and move all of the data from Contacts to the 'info' field in Groups so there is only one table, without having duplicate records for each contact.

+----+-------+----------------------------------------------+
| ID | Title |                     Info                     |
+----+-------+----------------------------------------------+
|  1 | Red   | Joe: joe@test.com                            |
|  2 | Blue  |                                              |
|  3 | Green | Billy: billy@test.com, Sally: sally@test.com |
+----+-------+----------------------------------------------+

Am I overstepping bounds by trying to use SQL to do this? I can't figure out how to aggregate multiple fields into one field using SQL. I am using MS Access so maybe I should look into using VBA to perform this right before I export the data.

Edit: Not a duplicate of Microsoft Access condense multiple lines in a table I am condensing multiple fields into one, not just one field into one.

Try the following link: https://stackoverflow.com/questions/5174362/microsoft-access-condense-multiple-lines-in-a-table/5174843#5174843 — Ankur Patel, Jul 11 '18 at 19:09
Possible duplicate of [Microsoft Access condense multiple lines in a table](https://stackoverflow.com/questions/5174362/microsoft-access-condense-multiple-lines-in-a-table) — June7, Jul 11 '18 at 19:16
Have you tried anything? Which part are you stuck at? Do you have the `join` between `Groups` and `Contacts`? — Mathieu Guindon, Jul 11 '18 at 19:17
FWIW you **don't** want your `Groups` table to contain information for multiple contacts. You want a *normalized* data set; the comma-separated result doesn't belong in a table, it belongs in a query/view, or on a report, or some client - don't store denormalized data, you'll regret it later. — Mathieu Guindon, Jul 11 '18 at 19:22
@MathieuGuindon Sorry, I realized I made a mistake in describing this. I am not modifying the original Groups table, only creating a query for a specific export. — user3606616, Jul 11 '18 at 19:46
Olivier's answer, and the linked question, seem to give you a very good starting point. — Mathieu Guindon, Jul 11 '18 at 19:47

Olivier Jacot-Descombes · Accepted Answer · 2018-07-11T19:38:57.007

You cannot do it with SQL alone. You need a VBA function. The function I show below uses the DAO library. Make sure to have a reference to the corresponding DLL (Microsoft DAO 3.6 Object Library).

Add this code to a module:

Public Function JoinRecords(dataSource As String, Optional delimiter As String = ";", _
                                  Optional columns As Long = 1) As String
    Dim db As DAO.Database, rs As DAO.Recordset, s As String, col As Long

    Set db = CurrentDb
    Set rs = db.OpenRecordset(dataSource, dbOpenForwardOnly)
    Do Until rs.EOF
        For col = 0 To columns - 1
            If s = "" Then
                s = Nz(rs(col))
            Else
                s = s & delimiter & Nz(rs(col))
            End If
        Next col
        rs.MoveNext
    Loop
    rs.Close: Set rs = Nothing
    db.Close: Set db = Nothing
    JoinRecords = s
End Function

Then you can run this query

UPDATE Groups
SET Info = JoinRecords("SELECT Name & ': ' & Contact FROM Contacts WHERE ID=" & ID, ", ")

Not familiar with Access, but isn't DAO referenced by default? — Mathieu Guindon, Jul 11 '18 at 19:24
It was referenced by default in older versions. Newer reference ADODB instead. I usually remove ADODB and add DAO because I prefer the latter. — Olivier Jacot-Descombes, Jul 11 '18 at 19:31

Combining Data into one Field Using MS Access and SQL

1 Answers1