Solved Process Automation - Concatenate field values into a list (1 Viewer)

GC1

New member
Local time
Today, 07:58
Joined
Apr 6, 2020
Messages
17
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

Nifty Access Guy
Staff member
Local time
Today, 07:58
Joined
Jul 9, 2003
Messages
16,245
You might find the example on my website useful:-

Transform, Concatenate Examples​


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

Super Moderator
Staff member
Local time
Today, 07:58
Joined
Feb 19, 2013
Messages
16,553
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

New member
Local time
Today, 07:58
Joined
Apr 6, 2020
Messages
17
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

New member
Local time
Today, 07:58
Joined
Apr 6, 2020
Messages
17
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

..forever waiting... waiting for jellybean!
Local time
Today, 15:58
Joined
May 7, 2009
Messages
19,169
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.
 
Last edited:

Minty

AWF VIP
Local time
Today, 07:58
Joined
Jul 26, 2013
Messages
10,355
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

New member
Local time
Today, 07:58
Joined
Apr 6, 2020
Messages
17
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

..forever waiting... waiting for jellybean!
Local time
Today, 15:58
Joined
May 7, 2009
Messages
19,169
create a Sub or Function in a Standard Module in VBA:
Code:
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
 
  • Like
Reactions: GC1

Users who are viewing this thread

Top Bottom