Solved Process Automation - Concatenate field values into a list

[GC1]

Hi,

I have repeat job each month that could do with some automation. There are c1500 records (in excel) each having a unique value in column A and change each month and there is no sequence, I need to concatenate these into a field separated by "," every 90 records. I thought this could be more a Access job than excel.

I don't have this is in a DB yet but basically trying to work out what is the best route for this?

Existing

Reference
10001
10002
10003

Ouput

Reference
10001,10002,10003...	Up to 90 records
10091,10092,10093...	Next 90 records
Etc
Etc

Many Thanks

 

[Uncle Gizmo]

You might find the example on my website useful:-

Transform, Concatenate Examples​

Display Field Data Horizontally - Nifty Access

Transform, Concatenate Examples A common requirement in MS Access is to extract Columnar data into horizontal data, for instance to make reports look better, to provide a list of emails suitable to send to with Outlook. In the YouTube videos below I explain how this can be done. Display Field...

www.niftyaccess.com

There is a sample dB here:- https://gum.co/HorizontalData

Contact me and I will explain how you can get a free Copy...

 

[CJ_London]

whatever field you put this in, it will need to be a long text field - short text will only allow 255 chars and you are going to need around 540

Using long text fields has it's limitations if you then need to do anything with it other than display (e.g. filter or sort). It is not the database way to store data like this.

Store vertically as it is at the moment, then use the concatrelated function to present the information - or perhaps UG's method which I've not looked at but sound similar

But might help if you explain why you need to do this

 

[GC1]

Thanks for this, Display Field Data Horizontally seems like the one I am after. I am guessing I need the loop to stop at 90 and start again on a new row? I wouldn't know where to start, let me make a start on a DB this week and go from there.

Again, thanks for your input.

 

[GC1]

whatever field you put this in, it will need to be a long text field - short text will only allow 255 chars and you are going to need around 540

Using long text fields has it's limitations if you then need to do anything with it other than display (e.g. filter or sort). It is not the database way to store data like this.

Store vertically as it is at the moment, then use the concatrelated function to present the information - or perhaps UG's method which I've not looked at but sound similar

But might help if you explain why you need to do this

Thanks, I don't intend on storing the data, this is to be exported into excel and used as part of a query in sql db through the user interface.

 

[arnelgp]

you need a Recordset to loop through the record:
Create a Linked table for you Excel file.

Dim db As DAO.Database Dim rst As DAO.Recordset Dim intCounter As Integer Dim strValue As String Set db = CurrentDb Set rst = db.OpenRecordset("TheLinkedExcelFile", dbOpenSnapshot, dbReadOnly) With rst If Not (.BOF And .EOF) Then .MoveFirst End If intCounter = 0 Do Until .EOF strValue = strValue & !Reference & "," .MoveNext intCounter = intCounter + 1 If intCounter > 89 Then strValue = Left(strValue, Len(strValue) - 1) db.Execute "Insert Into OutputTable (Reference) & " & _ "Select '" & strValue & "';" intCounter = 0 strValue = vbNullString End If Loop If Len(strValue) > 0 Then strValue = Left(strValue, Len(strValue) - 1) db.Execute "Insert Into OutputTable (Reference) & " & _ "Select '" & strValue & "';" End If .Close End With Set rst = Nothing Set db = Nothing

EDIT!!

Reference field in Output table should be Long Text (Memo) to prevent overflow of text.

 

[Minty]

Have you looked at possibly using a power query in Excel? (Just a suggestion rather than a definite solution, as It's quite powerful)

 

[GC1]

Have you looked at possibly using a power query in Excel? (Just a suggestion rather than a definite solution, as It's quite p

you need a Recordset to loop through the record:
Create a Linked table for you Excel file.

Dim db As DAO.Database Dim rst As DAO.Recordset Dim intCounter As Integer Dim strValue As String Set db = CurrentDb Set rst = db.OpenRecordset("TheLinkedExcelFile", dbOpenSnapshot, dbReadOnly) With rst If Not (.BOF And .EOF) Then .MoveFirst End If intCounter = 0 Do Until .EOF strValue = strValue & !Reference & "," .MoveNext intCounter = intCounter + 1 If intCounter > 89 Then strValue = Left(strValue, Len(strValue) - 1) db.Execute "Insert Into OutputTable (Reference) & " & _ "Select '" & strValue & "';" intCounter = 0 strValue = vbNullString End If Loop If Len(strValue) > 0 Then strValue = Left(strValue, Len(strValue) - 1) db.Execute "Insert Into OutputTable (Reference) & " & _ "Select '" & strValue & "';" End If .Close End With Set rst = Nothing Set db = Nothing

EDIT!!

Reference field in Output table should be Long Text (Memo) to prevent overflow of text.

Hi, please could you advise where do I insert this?

 

[arnelgp]

create a Sub or Function in a Standard Module in VBA:

Public Sub ConcatReference()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim intCounter As Integer
    Dim strValue As String
 
    Set db = CurrentDb
    Set rst = db.OpenRecordset("TheLinkedExcelFile", dbOpenSnapshot, dbReadOnly)
    With rst
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        intCounter = 0
        Do Until .EOF
            strValue = strValue & !Reference & ","
            .MoveNext
            intCounter = intCounter + 1
            If intCounter > 89 Then
                strValue = Left(strValue, Len(strValue) - 1)
                db.Execute "Insert Into OutputTable (Reference) & " & _
                            "Select '" & strValue & "';"
                intCounter = 0
                strValue = vbNullString
            End If
        Loop
        If Len(strValue) > 0 Then
            strValue = Left(strValue, Len(strValue) - 1)
            db.Execute "Insert Into OutputTable (Reference) & " & _
                        "Select '" & strValue & "';"
        End If
        .Close
    End With
    Set rst = Nothing
    Set db = Nothing
End Sub

on Immediate Window (VBA, if not showing press Ctrl-G):

ConcatReference