Vba to join master and transactional data

1. venuesParameters. The specific parameter sheet describing the required application.
2. geoCodingParameters. The common parameter sheet describing how to geoCode and other common parameter blocks
3. venueMaster. The list of known venues. This contains data about the venue, included the geoCoded addresses.
4. artistMaster. This is a lookup sheet that contains information about the artists that will be performing at particular venues
5. venueTransactions. This contains the list that associates venues with artists – thats to say a list of dates that artist x is performing and venue y
6. venueMapping. This is the result of joining 3,4 and 5 and is the only data that is input to the VizMap application.

1. Standard parameter setup , dSetsSetup
2. Pick up additional parameters, specific to the transaction data, that is only needed for this exercise getTransactionParameters.
3. Find any extra look up worksheets referenced (in this case the artistmaster), extraJoins.
4. Copy all the transactions from venuesTransactions to venuesMapping, bigCommit.
5. Add all the fields mentioned in the clone block are added
6. A new dSet is created, now containing all the columns to be cloned, plus the data from the original transactions
7. cDataSet has the capability of identifying a column as a key field when abstracting a block of data. It is this capability that allows the lookup of parameters through the syntax .cell(paramrow,paramcol).  When the venueMaster and artistMaster datasets were populated, the join keys Venue ID and Artist ID were set as their respective keys. This means that when you access a .cell(id,col) the id will refer to the Venue ID in the case of the venueMaster and the artist ID in the case of the artistMaster. This means that matching the venueTransactions to these look up files and copying over the cloned column does not even need any lookup code. The matching row is returned like this Set drk = dSets.DataSet(sMaster, True).Row(sId) and the matching cell in that row is copied by dr.Cell(sField).Value = drk.Cell(sField).Value
8. Commit all the cloned data, .bigcommit

joiningExample complete code.

Option Explicit
'for more about this
' https://ramblings.mcpher.com/classes/datamanip/
'to contact me
' https://gitter.im/desktopliberation/community
'reuse of code
' https://ramblings.mcpher.com/reusing-code-from-this-site/

Dim dSets As cDataSets
' this one looks copies over transactions, looks up the matching fields in the customer master
' and checks the paramater sheet to find what the important column headings are

' application 1 - orders/transactions
Public Sub transactionJoinOrders()
    transactionJoinExample cVizAppOrders
End Sub
' application 2 - venues/artists
Public Sub transactionJoinVenues()
    transactionJoinExample cVizAppVenues
End Sub
' application 3 - palaces
Public Sub transactionJoinPalaces()
    transactionJoinExample cVizAppPalaces
End Sub
' application 4 - orgs
Public Sub transactionJoinOrg()
    transactionJoinExample cVizAppOrg
End Sub
Public Sub transactionJoinExample(paramName As String)
    Dim hc As cCell, r As Range, dr As cDataRow, cc As cCell, sId As String, sField As String
    Dim rc As cCell, jExtend As String, drc As cDataRow, drk As cDataRow, sKey As String
    Dim sMaster As String, a As Variant, sOut As String
    
    jExtend = cJoin & "plus"
    Set dSets = dSetsSetup(paramName)
    If dSets Is Nothing Then Exit Sub
    
    If getTransactionParameters(paramName, dSets) Then
        If Not extraJoins(dSets) Then Exit Sub
        With dSets.DataSet(cTransactions)
            'clone that
            If .bigCommit(wholeSheet(dSets.DataSet(cName).Cell(cJoin, cSheet).toString), True) > 0 Then
            ' add some new columns if not already existing
                With dSets.init(wholeSheet(dSets.DataSet(cName).Cell(cJoin, cSheet).toString), , jExtend, , , True)
                    ' adding all new columns that need to be cloned
                    Set r = lastCell(.HeadingRow.Where)
                    For Each dr In dSets.DataSet(cCopyFields).Rows
                        ' does it  exist in the current set?
                        sKey = dr.Cell(cCopyFields).toString
                        a = Split(sKey, "=")
                        If (UBound(a) > LBound(a)) Then sKey = a(0)
                        If .HeadingRow.Exists(sKey) Is Nothing Then
                            Set r = r.Offset(, 1)
                            r.Value = sKey
                            
                        End If
                    Next dr
                End With
            Else
                MsgBox ("there was no data in " & cTransactions)
            End If
        End With
        
        ' read the completed data set structure with the extra headings back in
        With dSets.init(dSets.DataSet(jExtend).Where, , , , , True)
            'clone evyerthing
            For Each dr In .Rows
                For Each drc In dSets.DataSet(cCopyFields).Rows
                    ' the name of the column we want to touch
                    sField = drc.Cell(cCopyFields).toString
                    sOut = sField
                    a = Split(sField, "=")
                    ' there was a syntax like masterid=transid
                    If (UBound(a) > LBound(a)) Then
                        sField = a(1)
                        sOut = a(0)
                    End If
                    ' the master file to find it in
                    sMaster = drc.Cell(cCloneFrom).toString
                    ' the key to match on
                    sKey = dSets.DataSet(cName).Cell(sMaster, cJoin).toString
                    a = Split(sKey, "=")
                    ' there was a syntax like masterid=transid
                    If (UBound(a) > LBound(a)) Then sKey = a(1)
                    ' the target value of the key
                    sId = dr.Cell(sKey).toString
                    ' clear it out
                    dr.Cell(sOut).Value = Empty
                    ' get the matching row
                    Set drk = dSets.DataSet(sMaster, True).Row(sId)
                    ' maybe there is no match
                    If (drk Is Nothing) Then
                        MsgBox ("could not find value " & sId & " on Key " & sKey & _
                        " in dataset " & sMaster)
                    Else
                        Set cc = drk.Cell(sField)
                        If cc Is Nothing Then
                            MsgBox ("column " & sField & " does not exist in dataset " & cMaster)
                        Else
                            dr.Cell(sOut).Value = drk.Cell(sField).Value
                        End If
                    End If
                Next drc
            Next dr
            .bigCommit
        End With
    End If
    Set dSets = Nothing
End Sub

Private Function extraJoins(dSets As cDataSets) As Boolean
    ' we have to open additional dset for each copyfield not already known
    Dim dr As cDataRow, S As String, k As String, n As String, a As Variant
    extraJoins = False
    With dSets
    ' goes through the list of joind and creates a dataset from each corresponding sheet
        For Each dr In .DataSet(cCopyFields).Rows
            ' this is the field name
            n = dr.Cell(cCloneFrom).toString
            If .DataSet(n) Is Nothing Then
                ' means it doesnt yet exist - this would be the sheet name and key field its from
                With .DataSet(cName)
                    S = .Cell(dr.Cell(cCloneFrom).toString, cSheet).toString
                    k = .Cell(dr.Cell(cCloneFrom).toString, cJoin).toString
                End With
                a = Split(k, "=")
                If .init(wholeSheet(S), , n, True, , True, CStr(a(0))) Is Nothing Then Exit Function
            End If
        Next dr
    End With
    ' that went well
    extraJoins = True
End Function