[dba-VB] SPAM-LOW: Re: Merge rows

Arthur

 > The only potential fly in the ointment that I can see is What to do if the records being compared 
both contain values that do not match, i.e. A.Has_Dog = 'Y' and B.Has_Dog = 'N' (this could happen 
if the dog died between data collections.

And the answer is "nothing".  There is no way to determine date order of these records, they simply 
did not have that data in the original records.  Thus any answer is equally valid.  Take the FIRST 
answer and keep it.  Never update the first record FieldN if there is already data in that field.

This data is full of incongruities like this.  Income could rise or fall, children could leave home 
or die, spouse could divorce or die.  We cannot discover any of this kind of thing.

John W. Colby
www.ColbyConsulting.com


Arthur Fuller wrote:
> That's almost exactly what I would propose, JC, with perhaps a small
> revision. I was thinking that you could use a pair of recordsets, the first
> consisting of the first and only the first record with value xxx, and the
> second consisting of all the other instances of xxx. Then you can skip
> through the second recordset and compare each record with the record in the
> first recordset, updating only the single record with data from the current
> row in the second recordset. The idea is to update only one record in each
> group. Once that is done, then you can select top 1 of each group and group
> by the identifier. A Select Into would give you only the first record from
> each group. You could trash or rename the large table and the target table
> created by the Select Into would contain only the rows of interest,
> It occurs to me that you could probably even generate the compare/update
> statements, since they will be almost identical save for the given
> fieldname.
> 
> The only potential fly in the ointment that I can see is What to do if the
> records being compared both contain values that do not match, i.e. A.Has_Dog
> = 'Y' and B.Has_Dog = 'N' (this could happen if the dog died between data
> collections.
> 
> hth,
> Arthur
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
> 
>