Horizontal Concatenation with Queries (1 Viewer)

[ajetrumpet]

I've never needed to do this, but I've seen questions on the forum that suggested that it is needed for some situations. Someone once asked me how to concatenate more than 20 fields into a single field without writing it out manually in the SQL statement. Well, here's how:

SELECT concatfields("[COLOR="Red"]DATASET[/COLOR]", "[COLOR="red"]UNIQUE ID FIELDNAME[/COLOR]", [COLOR="red"]UNIQUE ID FIELD [U]VALUE[/U][/COLOR]) AS ConcatField

FROM [COLOR="red"]TABLE(S)[/COLOR]

Here's the accompanying function that makes it work:

Option Compare Database

Public db As DAO.Database
Public rs As DAO.Recordset

Function concatfields(dataset As String, _
                      uniquefieldname As String, _
                      uniquefieldvalue As variant) As String

Dim strExpr As String
Dim strTemp As String
Dim i As Long

Set db = CurrentDb
Set rs = db.OpenRecordset(dataset, dbOpenDynaset)

strExpr = uniquefieldname & "=" & uniquefieldvalue

rs.FindFirst strExpr

For i = 0 To rs.Fields.Count - 1
    strTemp = strTemp & rs.Fields(i) & [COLOR="Red"]"DELIMITER HERE"[/COLOR]
Next i

rs.Close

Set rs = Nothing
Set db = Nothing

strTemp = Left(strTemp, Len(strTemp) - 1)

concatfields = strTemp

End Function

Parameters
*DATASET - Can be a table, query or SQL statement
*UNIQUE ID FIELDNAME - Name of a field that holds a unique identifier for each record
*UNIQUE ID FIELDVALUE - Name of that field in []. For example, a field named "ID" would be written like: [ID]
*TABLE(S) - the "FROM" clause of the statement that would result from the DATASET variable, regardless of what it is. SQL statements involving JOINS also work, but the tables must be listed to avoid throwing errors.


For complex queries, a little duplication is needed. Here's a query from one of my examples where fields are being pulled from 3 different INNER JOINED tables and then concatenated by a delimiter:

SELECT concatfields(

[COLOR="DarkGreen"]'Dataset is an actual SQL statement[/COLOR]
"SELECT customers.customer, orders.orderdate, carriers.carrier, orders.orderid

FROM customers INNER JOIN (carriers INNER JOIN orders ON carriers.carrier = 

orders.carrier) ON customers.customer = orders.customer", "orderid", [orderid]) AS ConcatField

[COLOR="DarkGreen"]'FROM clause must be a duplicate of the dataset opened for JOINS[/COLOR]
FROM customers INNER JOIN (carriers INNER JOIN orders ON carriers.carrier = 

orders.carrier) ON customers.customer = orders.customer