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   | [email protected]   |
|  3 | Billy | [email protected] |
|  3 | Sally | [email protected] |
+----+-------+----------------+

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: [email protected]                            |
|  2 | Blue  |                                              |
|  3 | Green | Billy: [email protected], Sally: [email protected] |
+----+-------+----------------------------------------------+

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: stackoverflow.com/questions/5174362/… — Ankur Patel
– Ankur Patel, Commented Jul 11, 2018 at 19:09
Possible duplicate of Microsoft Access condense multiple lines in a table — June7
– June7, Commented Jul 11, 2018 at 19:16
Have you tried anything? Which part are you stuck at? Do you have the join between Groups and Contacts? — Mathieu Guindon
– Mathieu Guindon, Commented Jul 11, 2018 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
– Mathieu Guindon, Commented Jul 11, 2018 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
– user3606616, Commented Jul 11, 2018 at 19:46

Olivier Jacot-Descombes · Accepted Answer · 2018-07-11 19:38:57Z

2

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, ", ")

edited Jul 11, 2018 at 19:38

answered Jul 11, 2018 at 19:17

Olivier Jacot-Descombes

114k14 gold badges149 silver badges202 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mathieu Guindon Over a year ago

Not familiar with Access, but isn't DAO referenced by default?

Olivier Jacot-Descombes Over a year ago

It was referenced by default in older versions. Newer reference ADODB instead. I usually remove ADODB and add DAO because I prefer the latter.

user3606616 Over a year ago

Thanks, that's exactly what I needed!

Collectives™ on Stack Overflow

Combining Data into one Field Using MS Access and SQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related