How to copy data from header field to next records 1

[caroeliz]

I have a table with 10M+ records - only 3 fields: counter, date, id


1 01/01/05 1234567
2 01/02/05
3 01/01/04
4 01/01/05 2345678
5 12/12/03 3333333
6 13/12/03
7 01/01/02

etc

I need to copy id to the next record until id is not null and next id is null to repeat the process:

1 01/01/05 1234567
2 01/02/05 1234567
3 01/01/04 1234567
4 01/01/05 2345678
5 12/12/03 3333333
6 13/12/03 3333333
7 01/01/02 3333333



Control and ' is too slow, I need a query, macro or code to do this job.
Any ideas?

Many thanks.

Caroline

 

[lupins46]

This code should do it,(use your own table/field names) but for 10M records it's not going to be quick.
Back up your db first.


sub UpdateNos()
'assumes that the first record has a valid number
dim lastno as long

dim db as database
dim rs as dao.recordset
set db = currentdb
set rs = db.openrecordset("select * from mytablenamehere order by [Counter]", dbopendynaset)

rs.movefirst
lastno = rs!id

do until rs.eof
if isnull(rs!id) then
rs.edit
rs!id=lastno
rs.update
else
lastno = rs!id
end if
rs.movenext
loop

rs.close
set rs=nothing
set db = nothing

end sub

 

[caroeliz]

Cheers, very impressed with speed of response. What an excellent forum!

I'll try the code on Tuesday and let you know how it goes.