[AccessD] Merge records

A.D.

When I removed the first * from the query the "SQL Check" stopped giving me
errors.  Apparently the syntax:

"DELETE * FROM tblData" 

Is invalid in SQL Server, whereas:

"DELETE FROM tblData"

is valid.

Now that I am beyond that stumbling block, let me test and get back to you.

Thanks for all of your help.

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.TEJPAL
Sent: Monday, May 28, 2007 3:03 PM
To: Access Developers discussion and problem solving
Cc: ADT
Subject: Re: [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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com