[dba-VB] Merge rows

Stuart McLachlan stuart at lexacorp.com.pg
Mon Sep 21 17:55:29 CDT 2009


On the assumption that you don't really care which record you get if there are conflicts, I'd 
build a new table from:

Select Distinct, KeyField, Max(fld2),Max(fld3)..........

-- 
Stuart
 On 21 Sep 2009 at 23:45, Max Wanadoo wrote:

> Well, better brains then me will come up with the answer (are you there,
> Charlotte).
> 
> What I would do is
> 1. Add an AutoNum field to the table - let that run overnight.  This is
> good, as well you know. And you can make use of this from this point on.
> 2. set up the find-dupes wizard based on the natural key (!).
> 3. Extract the code from this.
> 4. put it into a module so that only those duplicate records are selected in
> a recordset
> 5. loop through the recordset.
> 6. do a secondary recordset based on the natural key where the autonum is
> not the same as the main record being looked at.
> 7. loop through the secondary records if found
> 8. update the main record.
> 9. delete the secondary record
> 10. move to next secondary record until none are left.
> 11. go to next main record.
> 
> Now, I am not saying that is the most efficient.  You may be able to do all
> this in an SQL statement.
> 
> But, hey! At least I am trying to help.
> 
> 
> Max
> 
> 
> -----Original Message-----
> From: dba-vb-bounces at databaseadvisors.com
> [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: 21 September 2009 23:31
> To: Discussion concerning Visual Basic and related programming issues.
> Subject: Re: [dba-VB] Merge rows
> 
>  > Do you NOT overwrite existing data.
>  > Or DO you ?
> 
> You do.  Basically if there are conflicts there is no way to discover which
> is the right answer, and 
> we are using the records to decide marketing stuff.  You can't have two
> answers in the end anyway. 
> In the end I "dedupe" (at the last step) to get rid of duplicates so ... it
> would be a one time 
> dedupe or dedupe every time you use the data.
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> 
> Max Wanadoo wrote:
> > Are you saying that the FIRST JohnColby record you encounter is therefore
> > the MASTER and the rest JohnColby record's data gets to update the MASTER?
> > 
> > So, 
> > Rec 1. John Colby likes Fish (at the time he might have liked fish)
> > Rec 2. John Colby does not like fish (might have been asked some time
> later,
> > after a bad meal)
> > 
> > Which takes precedence?
> > 
> > Do you NOT overwrite existing data.
> > Or DO you ?
> > 
> > Max
> 
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
> 
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
> 





More information about the dba-VB mailing list