[AccessD] merging records

    Sample subroutine named P_PopulateResultTableByAppQry() was sent in my previous post. Alternative routine, named P_PopulateResultTableByRecordset() is placed below. This one carries out the append action using AddNew method of recordset, instead of append query.

    An interesting feature of this routine is that you don't even have to bother about exempt fields or starting position of first survey field. It simply takes care of all fields.

Best wishes,
A.D.Tejpal
------------

Sample subroutine - for merging survey results
T_data is source table. Compacted results (ignoring
blanks) are appended to T_Result.
'====================================
Sub P_PopulateResultTableByRecordset()
    ' This subroutine merges the survey
    ' results (ignoring blanks) for each person in source
    ' table T_Data and appends the compacted outcome
    ' into destination table T_Result. Structure of T_Result
    ' is identical to that of T_Data
    
    Dim Qst As String, Txt As String
    Dim Fnm As String, Qst2 As String
    Dim Cnt As Long
    
    Dim rst1 As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim rst3 As DAO.Recordset
    Dim tdf As TableDef
    Dim db As DAO.Database
    
    Const SourceTable As String = "T_Data"
    Const DestnTable As String = "T_Result"
    
    Set db = DBEngine(0)(0)
    
    ' Clear destination table
    db.Execute "DELETE FROM " & _
                    DestnTable & ";", dbFailOnError
    
    Qst = "SELECT FirstName, LastName, " & _
            "Address FROM " & SourceTable & _
            " GROUP BY FirstName, " & _
            "LastName, Address;"
    Set rst1 = db.OpenRecordset(Qst)
    
    Set tdf = db.TableDefs(SourceTable)
    
    Set rst3 = db.OpenRecordset(DestnTable)
    
    Do Until rst1.EOF
        ' Append survey results (ignoring blanks), field-wise
        rst3.AddNew
        
        For Cnt = 0 To tdf.Fields.Count - 1
            Fnm = tdf.Fields(Cnt).Name
            Qst2 = "SELECT " & Fnm & _
                        " FROM " & SourceTable & _
                        " WHERE FirstName = '" & _
                        rst1.Fields("FirstName") & _
                        "' AND LastName = '" & _
                        rst1.Fields("LastName") & _
                        "' AND Address = '" & _
                        rst1.Fields("Address") & _
                        "' AND Len(" & Fnm & ") > 0;"
            Set rst2 = db.OpenRecordset(Qst2)
            
            ' This If/End If block is meant to prevent
            ' error (no current record) if rst2 is empty
            If rst2.RecordCount > 0 Then
                rst3.Fields(Fnm) = rst2.Fields(0)
            End If
        Next
        
        rst3.Update
        
        rst1.MoveNext
    Loop
    
    rst1.Close
    rst2.Close
    rst3.Close
    Set rst1 = Nothing
    Set rst2 = Nothing
    Set rst3 = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Sub
'====================================

  ----- Original Message ----- 
  From: jwcolby 
  To: 'Access Developers discussion and problem solving' 
  Sent: Friday, November 16, 2007 00:57
  Subject: Re: [AccessD] merging records


  A.D.

  I will get around to this, just not sure when.  Running this through Access
  / VBA is probably a non starter since not only are there a lot of fields
  (>600), but also a lot of records (>50 million).  Thus if this is ever to
  actually work it will need to be done in VB.Net.  My experience so far is
  that VB.Net speeds things up by a factor of 10 or more.

  Even then I would suggest something like pulling the field names that will
  be actually used out and placing them (or maybe their numeric field position
  in the record object) in a collection.  That way the code that iterates the
  fields becomes a simple "for each in colValidFld" construct instead of a
  constant comparison to see if they are in the string of non-valid fields.

  If you place the valid field names in the collection, then the For Each loop
  would "just have" the field names to build up the SQL statement.
  Additionally I think you can use the field names to index into the field
  collection to extract the values:

  rst.fields(strFldName).value

  Believe me, with 600 fields and 50 million records, it needs all the
  efficiency it can get.

  And BTW thanks for the code.

  John W. Colby
  Colby Consulting
  www.ColbyConsulting.com