[AccessD] Merge records

If you think about it, JC, DELETE * is kind of redundant. What else could
you mean?. You can't delete all the values in a column, for example. You can
DELETE WHERE but that is still all the columns.

On 5/29/07, jwcolby <jwcolby at colbyconsulting.com> wrote:
>
> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>