Multiple rows in same row by query of two tables

[drag1c]

Hi,

I am using two tables, T1 and T2.

T1 has Material, Work plan
T2 has Work plan, Work plan Owner

How I can connect by query these two tables, but to Material is unique value in 1st column and Work Plan Owners are written in 2nd column with semicolon as delimiter?
Example:

Material	Work Plan Owners
Material1	John; Peter; Jane
Material2	Michael; Thomas

 

[smozgur]

One way of doing this could be creating the missing concat function in Access (I think it still doesn't exist). But you need a custom function, so you need VBA.

Create a custom function in a new module in VBA.
(Assuming the Work plan field is a number field as an ID, otherwise, the SQL string should be changed. Please let me know if that's the case).

Function Concat(wpId As Integer) As String
Dim owners() As String
With CurrentDb.OpenRecordset("SELECT Owner FROM T2 WHERE WorkPlan = " & wpId, dbOpenForwardOnly)
    Do While Not .EOF
        ReDim Preserve owners(1 To .RecordCount)
        owners(UBound(owners)) = .Fields("Owner")
        .MoveNext
    Loop
    Concat = Join(owners, ";")
    .Close
End With
End Function

Now you have the function and use it in a query. Create a query by using the following SQL string:

SELECT T1.Material, Concat([WorkPlan]) AS Owners FROM T1;

My sample data set is shown below (ignore the ID fields).
T1:

T2:

And. the result of the query:

 

[drag1c]

Dear Smozgur,
I am using ADODB in Excel to pull data. I am not sure it could work this way.
Could this VBA Function work through excel or it should be modified somehow?

 

[smozgur]

No, it shouldn't work when you load the query by using ADODB in Excel, because it is in a module in VBA. ADODB cannot access the user defined functions, so the query should result in error.

I would do something like this (in Excel). Use your own connection string and adapt the field names to test it.

Sub getDataInMyDesiredFormat()
Dim conn As ADODB.Connection
Dim rsMaterial As ADODB.Recordset
Dim rsWorkplan As ADODB.Recordset
Dim owners As Object 'Dictionary
Dim strWorkPlan As String
Dim strOwner As String
Dim sht As Worksheet
Dim rng As Range

    Set conn = New ADODB.Connection
    With conn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=C:\somewhere\Database1.accdb"
        .Open
    End With
   
    Set rsWorkplan = New ADODB.Recordset
    With rsWorkplan
        .Open "SELECT WorkPlan, Owner FROM T2 ORDER BY WorkPlan", conn, adOpenForwardOnly, adLockReadOnly
        Set owners = CreateObject("Scripting.Dictionary")
        Do While Not .EOF
            strWorkPlan = .Fields("WorkPlan").Value
            strOwner = .Fields("Owner").Value
            If owners.Exists(strWorkPlan) Then
                owners(strWorkPlan) = owners(strWorkPlan) & ";" & strOwner
            Else
                owners(strWorkPlan) = strOwner
            End If
            rsWorkplan.MoveNext
        Loop
        .Close
    End With
   
    Set sht = ActiveWorkbook.Worksheets.Add
    Set rng = sht.Cells(1, 1)
    rng.Resize(, 2).Value = Array("Material", "Owners")
   
    Set rsMaterial = New ADODB.Recordset
    With rsMaterial
        .Open "SELECT * FROM T1", conn, adOpenForwardOnly, adLockReadOnly
        Do Until .EOF
            strWorkPlan = .Fields("WorkPlan").Value
            Set rng = rng.Offset(1)
            rng.Resize(, 2).Value = Array(.Fields("Material").Value, owners(strWorkPlan))
            .MoveNext
        Loop
        .Close
    End With
   
    conn.Close
End Sub

Note: I can't test it at the moment, but I think Power Query could be also used to get the result you need.

 

[drag1c]

I've managed it work to not throw errors, but output does not have addition on second column. Simply, printout is like just JOIN is done.

I know to do this in power query, actually it's pretty simple. I will try it.

Thanks for the tip !

 

[Joe4]

I know to do this in power query, actually it's pretty simple.

What does the query look like in Power Query?
It may be possible to do a similar query in Access.

 

[smozgur]

I've managed it work to not throw errors, but output does not have addition on second column. Simply, printout is like just JOIN is done.

I can't say more without seeing your table structure since the code works as it should with my sample structure. You just need to copy and paste and then change the necessary constants (file name, field names, etc) in it.

Power Query implementation: Again - you need to make changes according to your table structure.

let
    Db = Access.Database(File.Contents("C:\somewhere\Database1.accdb")),
    T1 = Db{[Item="T1"]}[Data],
    T2 = Db{[Item="T2"]}[Data],
    Merge = Table.NestedJoin(T1, {"WorkPlan"}, T2, {"WorkPlan"}, "T2", JoinKind.LeftOuter),
    Combine = Table.AddColumn(Merge, "Owners", each  Text.Combine([T2][Owner], ";")),
    Result = Table.SelectColumns(Combine,{"Material", "Owners"})
in
    Result