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

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