Store all records in one field

[le_sayan]

Hello,

I have a table like this :

Fields 1 --- field 2

A --- tuesday
A --- wednesday
A --- thursday
B --- tuesday
B --- wednesday

I want to end like this :

Fields 1 --- field 2

A --- tuesday,wednesday,thursday
B --- tuesday, wednesday


I was thinking of doing a loop in vba with recordset and a filter on field1 and concatenate the days of field2 in the first reccord of each letter.
But my table is big so my code needs to be fast.

Any idea how to do this ?

Thank you in advance.

 

[Galaxiom]

http://allenbrowne.com/func-concat.html

Use it in a query. It would be wrong to change the table to that structure.

 

[dbay]

Even though it is not the correct way to store data in a "typical" database. Here is a solution.

Concatenate your data using a delimiter ("," or ";").

Here is an example to retrieve the data.

Private Sub Get_Data()
 
    Dim DB As DAO.Database
    Dim RS As DAO.Recordset
    Dim VAR_Array As Variant
    Dim i As Integer
 
On Error GoTo err_proc
 
    Set DB = CurrentDb()
    Set RS = DB.OpenRecordset("SELECT * FROM Your_Table WHERE Table_Index = " & Some_Index & ";", dbOpenSnapshot)
    If Not (RS.BOF And RS.EOF) Then
        For i = 1 To 99 'These are the actual Columns in your table. (Up to 255)
            If Not IsNull(RS(i)) Then
                VAR_Array = VBA.Split(RS(i), ";") 'You can change this to the delimiter your using
                Me.TXT_Textbox_1 = VAR_Array(0)
                Me.TXT_Textbox_2 = VAR_Array(1)
                Me.TXT_Textbox_3 = VAR_Array(2)
                Me.TXT_Textbox_4 = VAR_Array(3)
            End If
        Next i
    End If
    RS.Close
    DB.Close
 
exit_proc:
On Error Resume Next
    Set RS = Nothing
    Set DB = Nothing
    Exit Sub
 
err_proc:
    Resume exit_proc
 
End Sub

 

[le_sayan]

Thank you Guys,

I used the func-concat and and does exactly what I needed.