[AccessD] Merge records

John,

    Sample update query given below, will update fields F1 to F4 in table tblData. If any field is empty, it will get filled in by the first significant value occurring in any record for this field, for the given HashPerson. This query makes use of function Fn_MergedValue(), also given below.

    After updating, duplicate records can be removed by running the sample delete query as given below.

    For a large sized table with large number of fields, if serious slow down is experienced while running the update query, you could consider carrying out the update process for a few fields at a time 


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

Query for updating fields F1 to F4
(If the field is empty, first available value)
======================================
UPDATE tblData SET tblData.F1 = Fn_MergedValue("F1",[F1],[HashPerson]), tblData.F2 = Fn_MergedValue("F2",[F2],[HashPerson]), tblData.F3 = Fn_MergedValue("F3",[F3],[HashPerson]), tblData.F4 = Fn_MergedValue("F4",[F4],[HashPerson]);
======================================

Query for deleting duplicate HashPersons
======================================
DELETE * FROM tblData 
WHERE (SELECT Count(*) FROM tblData As T1 WHERE T1.HashPerson = tblData.HashPerson AND T1.PKID <= tblData.PKID) > 1;
======================================

Fn_MergedValue() - User defined function
======================================
Function Fn_MergedValue( _
                ByVal FieldName As String, _
                ByVal FieldValue As Variant, _
                ByVal HashPersonValue _
                As String) As String
    Dim Qst As String, Fdv As String
    Dim rst As DAO.Recordset
    
    Fdv = Nz(FieldValue, "")
    ' If field has significant value, retain it
    If Len(Fdv) > 0 Then
        GoTo ExitPoint
    End If
    
    ' If field is empty, fill in the first significant value
    ' occuring in any record for this field, for the
    ' given HashPerson
    Qst = "SELECT TOP 1 " & FieldName & _
            " FROM tblData WHERE  " & _
            "HashPerson = '" & HashPersonValue & _
            "' And Len(" & FieldName & ") > 0;"
    Set rst = DBEngine(0)(0).OpenRecordset(Qst)
    
    If rst.RecordCount > 0 Then
        Fdv = Nz(rst.Fields(0), "")
    End If
    
ExitPoint:
    
    Fn_MergedValue = Fdv
    
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    On Error GoTo 0
    
    ' Note - For repetitive use, DBEngine(0)(0)
    '            is preferred over CurrentDb
End Function
======================================

  ----- Original Message ----- 
  From: jwcolby 
  To: 'Access Developers discussion and problem solving' ; dba-sqlserver at databaseadvisors.com 
  Sent: Sunday, May 27, 2007 23:25
  Subject: [AccessD] Merge records


  I have a situation where I might have several data records in a table (the
  big 700 field table) that are about the same person / address but have
  different data in some of the other fields.  These records represent answers to surveys and so if a person answered three surveys, the person could have three records in the database.  I need to merge the data from the three records into a single record, and eventually delete the other two.

  I have developed a field that represents the SHA1 hash of the address, zip5, zip4, lastname and firstname.  I am running some tests to see whether this hash is unique across 50 million records (unique to that name / address) but I suspect that it will be.  Once I determine that it is, then I can use that single field as a single "person identifier" field.

  So I need pointers how to "merge" the data from one record into a second record, only merging fields where there is legitimate data, and not overwriting fields where there is already data.

  John W. Colby
  Colby Consulting
  www.ColbyConsulting.com