0

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".

Vehlad
  • 155
  • 2
  • 10
  • 20
  • 4
    Any reason why you would like to do that? Missing id in one autonumeric field happen all the time and dont affect the performance. You could change it, but then you will need to also update related tables. And that can be a mess – Juan Carlos Oropeza Sep 21 '15 at 14:41
  • 3
    Identities should *never* change. They represent that particular record, and if you were to update them, you would have to update everything else that has a reference to it. To put it into a real-world perspective, would you expect to have to change your Social Security Number every time someone else died and shifted them forward? – Siyual Sep 21 '15 at 14:46
  • 1
    OP could be using INO as an "invoice Number" and may want to keep a sequence for accounting purposes. My recommendation if that is the case to never delete invoice records from a database. Instead, flag them as deleted using a field, so your reports that generate invoice documents can produce a "skipped" or "deleted" record for auditors. – Greg Viers Sep 21 '15 at 15:14
  • yes @TheTTGGuy is correct we dnt have any link of tables or joins or any customer social security numbers or ids which will be affected. we want just correct sequence for accounting purpose same as most accounting software does on deletion or insert of entry. they automatically adjust the invoice number, receipt number etc. in proper sequence. – Vehlad Sep 21 '15 at 16:42
  • @JuanCarlosOropeza It is not Auto Number field & there is no related table or join used. According to Indian Accounting System & Taxation Department:- Invoice Numbers should be properly numbered and in sequence. Deleted a lot of records of wrongly generated/cancelled invoices, But later recognized that software is not auto numbering (While most of indian accounting softwares does that) so we want all entries to be correctly numbered. – Vehlad Sep 21 '15 at 17:33

3 Answers3

1

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

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

Hope this helps

Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
  • Thanks @ankit But it starts the INO sequence from 18347. I already posted my select statement. There are other records which need to be filtered before with WHERE TYPE=True i.e. only those invoice which are paid others records are invoice generated but not paid. hence no accounting entry. I need to renumber only "Type=True" rows. – Vehlad Sep 21 '15 at 17:01
  • Since i know the starting number 1430 & every row should be +1. can i use UPDATE command with Where Clause so that automatically increase each row with INO = 1430 next INO = 1430+1 next INO = 1430+2 just like counter ? – Vehlad Sep 21 '15 at 17:07
  • thanks @ankit bajpai for the answer that helped to achieve this. – Vehlad Sep 22 '15 at 09:53
1

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.

Beth
  • 9,531
  • 1
  • 24
  • 43
  • there is no auto number field INO is set to NUMBER data type auto numbering is handled by software while saving. – Vehlad Sep 21 '15 at 17:05
  • 1
    right, so you can create a new one. it will do your numbering for you. – Beth Sep 21 '15 at 17:19
1

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

asdev
  • 943
  • 6
  • 9
  • Thanks @asdev for the help that is exactly required i want to integrate select statement in this function :) – Vehlad Sep 22 '15 at 09:52