CODEWITHSUNDEEP
sqlvbams-access
user3606616
user3606616

Reputation: 33

Combining Data into one Field Using MS Access and SQL

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.

Upvotes: 0

Views: 104

Answers (1)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112324

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

Upvotes: 2

Related Questions