Max Wanadoo
max.wanadoo at gmail.com
Mon Sep 21 17:59:37 CDT 2009
50 Million! Max -----Original Message----- From: dba-vb-bounces at databaseadvisors.com [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan Sent: 21 September 2009 23:55 To: Discussion concerning Visual Basic and related programming issues. Subject: Re: [dba-VB] Merge rows 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 > _______________________________________________ dba-VB mailing list dba-VB at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-vb http://www.databaseadvisors.com