Microsoft Access 2016 Summarize and Merge Table with SQL

Question

I have a table that I need to format for use in a manual upload process. The table is thousands of rows so I need to use a repeatable process to quickly fix the way the data is given to me into what it needs to be. I have zero ability to control the way the data comes to me today. But, I have to format it to use it due to a system limitation. My current table is 4 columns, I need to output it as 3 columns. I have to group up by field names: "brand" and "promotion". Field name: "skus" I need to take and merge them into one continuous string by a single "brand" and "promotion" combination.

There are duplicate "promotion" by a given brand since they are created at a product level. But, the system they go into need to be "brand", "promotion", "skus".

Not really sure if I need to use VBA to do some of this inside Access. Or I can do this in two different queries.

Answer 1

You will need to use a bit of VBA to do this. The VBA will need to loop a recordset of data from the table that is filtered on brand and promotion and build up the sku string. Something like this perhaps:

Function fJoinData(strBrand As String, strPromotion As String) As String
    On Error GoTo E_Handle
    Dim db As DAO.Database
    Dim rsData As DAO.Recordset
    Dim strSQL As String
    Set db = DBEngine(0)(0)
    strSQL = "SELECT skus FROM Table1 " _
        & " WHERE Brand='" & strBrand & "' " _
        & " AND Promotion='" & strPromotion & "';"
    Set rsData = db.OpenRecordset(strSQL)
    If Not (rsData.BOF And rsData.EOF) Then
        Do
            fJoinData = fJoinData & ", " & rsData!skus
            rsData.MoveNext
        Loop Until rsData.EOF
    End If
    If Left(fJoinData, 2) = ", " Then fJoinData = Mid(fJoinData, 3)
fExit:
    On Error Resume Next
    rsData.Close
    Set rsData = Nothing
    Set db = Nothing
    Exit Function
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "fJoinData", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume fExit
End Function

Rather than inserting into a table, I would suggest just creating a query which can then be exported:

SELECT DISTINCT 
    T.Brand, 
    T.Promotion, 
    fJoinData(T.Brand,T.Promotion) AS skus
FROM Table1 AS T

Regards,