update query for numbering records 1

[Niebotel]

I have a table (Ontvangsten) with two number fields: IDIn and ID
In IDIn are numbers like 1312, 1313, 1314, 1320 etc. so in the right sequence but not starting from 1 and having missing numbers of deleted records
(this is an autonumber field)
Now I want to number the records in ID like 1, 2 , 3, 4 (these are numbers for the user) ID is defined as Numeric
Is there a simple query which can do that?

 

[dhookom]

You can try something like:

UPDATE Ontvangsten
SET ID = DCount("*","Ontvangsten","IDIn<=" & [IDIn])

This could take a while since DCount() is not very efficient.

Duane
Hook'D on Access
MS Access MVP