Auto Number Update in a table after delete record

[SwingXH]

I have a form which shows the records of the table below:
JobID Name Hours DueDate
1 AAA 10 June 6, 2004
2 BBB 50 July 6, 2004
3 CCC 100 April 20, 2004
4 AAA 20 May 30, 2004
5 BBB 40 Aug 30, 2004

The user can add and delete any records, my question is that after one of the record is deleted, the JobID does not update. Assume JobID is an autonumber/(or integer) just show the sequence of the jobs. When I deleted record 4, I wish the rest of the records will automatically reorder, which means 5 becomes 4 since 4 has been deleted.
How?
Thanks!

SwingXH

 

[scriverb]

You need to read up on AutoNumber fields. They do not renumber after a delete. An individual number is used once and only once. It is used mostly for programming purposes to uniquely identify a record and should not be renumbered. If you need a numbering system to show a continuous number for your records you can do that through a query. We can help with that. Then you will have a unique number for each record in the recordset starting at 1 through the number of records in the query selection.

Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SwingXH]

Thanks scriverb. Assume I have another field ID as a numbering system that you mentioned. Can you help me on creating the query? With AutoNumber, when I hit add new record, a new number follow the last will show up. Can I still have this function with ID which is not an AutoNumber?

Thanks a lot!
SwingXH

 

[KenReay]

Hi

Think about what you are saying here

If you renumber the JobId after a delete, that could mean thousands of updates, since every row with a higher Job Id thatn the deleted record must be updated. Worse, if you have relationships to other tables, which depend on that Id, then all of those rows must be updated too. I assume performance is not relevant in your application and a response time measured in minutes or even hours would be acceptable?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[scriverb]

Yes, you can but the question is "Why???". You see ACCESS stores you records in the order of last entry. Starts at the beginning and just adds a records on the end of the table. It is the indexing and data values that place the records in their proper order. You may add a field with a name such as "Bob Adams". It is the last record in the table so its ID would be maybe 9,125 but if you look at it in a report or a query and it is sorted by last name it is going be mabe the 5th record displayed and certain the ID number 9125 means nothing.

But when you display the records without using a new field called ID but only displaying a calculated record number within the recordset the numbers will start at 1 and end at 9,125.

I always try to get people away from using a numerical ID field that has some mental value of positioning or number of records in the table. It really should mean nothing and be of no consequence. There are better ways of referencing a record. Think of how you want to find a record. It surely would not be by this number. It should be by Name, SS#, EmployeeID, etc. But not just the record # in the table. If you delete number 10 do you really want to renumber everyone from 11 thru 9.125 down one? These are questions you have to ask and try to rethink your position on this field.

I will be leaving for a while and won't be able to respond back until later today. Let me know if I can help further.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SwingXH]

Ken, you are correct though my database is not that big. I will use keep the autoNumber for relationship use, but would like to use an additional ID for numbering the system.

SwingXH

 

[SwingXH]

scriverb,thanks for your comments and suggestons. I am new to database, this is a good lesson for me.

Could you and Ken please help me on this question?

http://www.tek-tips.com/viewthread.cfm?SQID=871312&SPID=701&newpid=701&page=1

SwingXH