Create a string from several records

[MICKI0220]

I have a work table which has two fields in it, a person's id and the deduction description. The person's Id is the matching field in all records. Basically I could group on this field if the other field was a sum or some sort. However the Deftinition is text and I need to create a string of all of them if. So basically I need to create one record out of this table that has the person's ID and then all the records definitions concatenated as a string. Does anyone know how I can do this? Here is an example.

ID Description

25093 401K
25093 Child Support
25093 Life Insurance




Thank you for you help with this.

Micki

 

[Andrzejek]

So you are trying to create this:

25093,Child Support,401K,Life Insurance

???

Have fun.

---- Andy

 

[MajP]

faq701-4233

 

[MICKI0220]

Sort of. I guess I should figure out how to separate my fields up there. The record should be under ID Column Header, just 25093, and then under Description column header should be (Child Support,401K,Life Insurance)

 

[MICKI0220]

I think I should mention that there may be several groups of id's such as three records with the same id, 1 record with another id and then maybe 4 of another id. What it needs to do is group the id's together and then concatenate the Description field of the same id's

 

[dhookom]

How far did you get with using the Concatenate() function? Can you share your SQL?

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

I would try something like that in VBA:

Dim rst2 As ADODB.Recordset

Sub Something()
Dim rst1 As ADODB.Recordset
Dim str As String
Dim i As Integer

str = "Select * From MyTable Order By ID, Description"

rst2.Open str, Conn

str = "Select Distinct ID From MyTable Order By ID"

rst1.Open str, Conn

For i = 1 to rst1.Recordcount
    MsgBox ContDesc(rst1!ID.Value)
    rst1.MoveNext
Next i

rst1.Close
Set rst1 = Nothing

rst2.Close
Set rst2 = Nothing

End Sub

Function ContDesc(lngID As Long) As String
Dim z As Integer
dim t As String

rst2.Filter = "ID = " & lngID

For z = 1 to rst2.RecordsCount
    If z = 1 Then
        t = rst2!Description.Value
    Else
        t = t & "," & rst2!Description.Value
    End IF
    rst2.MoveNext
Next z

ContDesc = t

End Sub

Have fun.

---- Andy