Making multiple records (rows) into one & concatenating text

[pkhusby]

I checked other threads and could not find something similar to what I need to do (maybe because I don't know what to call it!) Here it is ...

I have a table structured like this

A Text1 (first column numeric, second text)
A Text2
A Text3
B Text4
C Text5
C Text6

What I want to make is a table like this:

A Text1 Text2 Text3
B Text3
C Text5 Text6

Cross tabs don't quite get me there.

What I want to do with the second table is concatenate the text so it looks like this:

A Text1&Text2&Text3
B Text3
C Text5&Text6

Thanks for any help!

Per

 

[scriverb]

I posted a Function in the following thread:

thread703-495410

With a few modifications I believe it could provide you what you want. Let me know and I can help you with this if you decide this will work for you.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[fneily]

I made a table with two fields - partno, desc. Desc can span more than one record, eg.
partno desc
abcd this is one desc
abcd this is two desc
xyz another desc
xyz more desc
etc. etc.

I want to concatenate the desc's into one field.

create a query that looks like this:
SELECT [partno], fConcatFld("parttable","partno","desc","string",[partno]) AS descs
FROM parttable
GROUP BY [partno];

Do ALT+F11, insert a new module and put the following code for the fConcatFld function:

Function fConcatFld(stTable As String, _
stForFld As String, _
stFldToConcat As String, _
stForFldType As String, _
vForFldVal As Variant) _
As String

'Usage Examples:
' ?fConcatFld(("Customers","ContactTitle","CustomerID", _
' "string","Owner&quot
'Where Customers = The parent Table
' ContactTitle = The field whose values to use for lookups
' CustomerID = Field name to concatenate
' string = DataType of ContactTitle field
' Owner = Value on which to return concatenated CustomerID
'
Dim lodb As Database, lors As DAO.Recordset
Dim lovConcat As Variant, loCriteria As String
Dim loSQL As String

On Error GoTo Err_fConcatFld

lovConcat = Null
Set lodb = CurrentDb


loSQL = "SELECT [" & stFldToConcat & "] FROM [" & stTable & "] WHERE [" & stForFld & "] = '" & vForFldVal & "' ;" 'place this line on one line in the VBA window.

Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)

'Are we sure that duplicates exist in stFldToConcat
With lors
If .RecordCount <> 0 Then
'start concatenating records
Do While Not .EOF
lovConcat = lovConcat & lors(stFldToConcat) & &quot; &quot;
.MoveNext
Loop
Else
GoTo Exit_fConcatFld
End If
End With

'Just Trim the trailing ;
fConcatFld = Left(lovConcat, Len(lovConcat) - 1)


Exit_fConcatFld:
Set lors = Nothing: Set lodb = Nothing
Exit Function

Err_fConcatFld:
MsgBox &quot;Error#: &quot; & Err.Number & vbCrLf & Err.description
Resume Exit_fConcatFld
End Function

Run the query, which calls the function. Besure in your VBA window, click on Tools , the References and make sure the DAO library is referenced first.

Neil

 

[pkhusby]

Thanks Neil - I understand the VBA code pretty much. Not sure how to make the query you mention near the top using SELECT.

I will be trying this.

Per

 

[fneily]

SELECT [numeric], fConcatFld(&quot;YourTableName&quot;,&quot;numeric&quot;,&quot;text&quot;,&quot;string&quot;,[numeric]) AS descs
FROM YOURTABLENAME
GROUP BY [numeric];

Numeric is your first field column name
Text is your second field column name
YourTableName is the table name

Goto design view of a query, click the down arrow next to the View button and select SQL VIEW. Then type in the SQL.

Neil