CODEWITHSUNDEEP
sqlms-accessms-access-2007ms-access-2000
Vehlad
Vehlad

Reputation: 155

Fill a Number field with Auto Increment values in MS ACCESS

I have a Access Table with following structure:

INO   IDATE      NAME ADDRESS CITY PHONE CONTACT & Other Fields.....
1430  01-Apr-15 
1431 01-Apr-15 
1432 01-Apr-15 
1435 01-Apr-15 
1436 01-Apr-15 
1440 01-Apr-15 
1441 01-Apr-15 
1448 01-Apr-15 
1450 01-Apr-15 
1455 02-Apr-15 
1456 02-Apr-15 
1459 02-Apr-15

Field "INO" is set To Data type "NUMBER" Field Size: Double Indexed= Yes

This field is auto increment programmatically, but after deletion of some useless & cancelled records from database. INo. series is not in order. I want to re generate the numbers & fill the gap of deleted numbers. i.e. INO 1435 should be 1433, 1436 to 1434, 1440 to 1435 & so on.

Select Statement i use to filter records from table:

SELECT *
FROM IDETAIL
WHERE TYPE=True AND INO>=1430 ORDER BY INO;

Till 1430 records are in order there is gap after 1430 & dated 01-Apr-15. there are about 18,000 records in table so not easy to manually correct the records. How can i correct this with SQL or any other command.

P.S. : No related table or entry is effected. We just want to maintain the sequence of entries for accounting purpose.

Just like we use "Do While" function. Starting entry INo is correct and known. All the entries got numbered in a loop starting with 1st record INo=1430, next record INO = 1430+1, next 1430+2 until last entry. How to implement same in UPDATE query alongwith "WHERE TYPE=True".

Upvotes: 0

Views: 5979

Answers (3)

asdev
asdev

Reputation: 943

Using and improving the great answer from @Ankit Bajpai here is maybe what you need:

UPDATE (SELECT * FROM IDETAIL WHERE TYPE=True AND INO>=1430 ORDER BY INO) 
SET INO = DCount('*', 'IDETAIL ', 'INO <=' & INO & ' AND INO >= 1430  AND Type = True') + 1429

Be aware that if INO is indexed and without duplicates, it may create errors in the recordsets Type=false

Upvotes: 1

Beth
Beth

Reputation: 9607

If you insert a new autonumber field into the table, it should generate a new set of IDs for the rows. Then, if you want, you can update your existing field to the new ID value and delete the autonumber field.

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

If no table is referenced from this table, You can use following query:-

UPDATE YourTable
SET INO = DCount('*', 'YourTable', 'INO <= ' & INO)

Hope this helps

Upvotes: 1

Related Questions