Help With SQL Update With Duplicate Records

[Andy]

Hi Gang

I have a simple table with some duplicate values.

Field1 Field2 Field3
Andy Smith 10
Andy Smith 10
Andy Smith 10

With an SQL Update statement, how would I change the table to look like

this. I need to increment Field3 by 10 for each record.

Field1 Field2 Field3
Andy Smith 10
Andy Smith 20
Andy Smith 30

Please help.
Andy

[Tom van Stiphout]

On 17 Mar 2006 11:20:34 -0800, "Andy" <an***********@siemens.com>
wrote:

That's really hard to do with a sql update statement. Rather process
the table using a recordset.

-Tom.

Hi Gang

I have a simple table with some duplicate values.

Field1 Field2 Field3
Andy Smith 10
Andy Smith 10
Andy Smith 10

With an SQL Update statement, how would I change the table to look like

this. I need to increment Field3 by 10 for each record.

Field1 Field2 Field3
Andy Smith 10
Andy Smith 20
Andy Smith 30

Please help.
Andy

[Andy]

Tom

How would I do this with a record set? Would I load all the records
into a recordset, do my data manipulation, delete all the records in
the table and then write the records in the recordset back to the empty
table?

Please help.

Andy

[Tom van Stiphout]

On 20 Mar 2006 10:15:04 -0800, "Andy" <an***********@siemens.com>
wrote:

You wanted to go from:

Field1 Field2 Field3
Andy Smith 10
Andy Smith 10
Andy Smith 10
Jim Jones 10
Jim Jones 10
to:Field1 Field2 Field3
Andy Smith 10
Andy Smith 20
Andy Smith 30
Jim Jones 10
Jim Jones 20
'aircode:
dim db as dao.database
dim rs as dao.recordset
dim strField1 as string
dim strField2 as string
dim lngField3 as long
set db=currentdb()
set rs=db.openrecordset("select * from MyTable order by Field1,
Field2, Field3", dbOpenDynaset)
while not rs.eof
if rs!Field1=strField1 and rs!Field2=strField2 then
'still on same firstname/lastname: increment by 10
rs.edit
lngField3=lngField3+10
rs!Field3=lngField3
rs.update
else
'new firstname/lastname: initialize variables
strField1=rs!Field1
strField2=rs!Field2
lngField3=0
end if

rs.movenext
wend
rs.close

-Tom.
Tom

How would I do this with a record set? Would I load all the records
into a recordset, do my data manipulation, delete all the records in
the table and then write the records in the recordset back to the empty
table?

Please help.

Andy