hi,
i'm new to access so limited knowledge of workings.

i've been asked to append a table of names, addresses, phone numbers etc.
the table has been going for several years and names have been deleted for certain reasons and new names added.

the first column is an autonumber and is also headed as a user id. the person who owns the database (but did not create it) asked me if it was possible to start the numbers again in proper succession for every record without numbers missing where records have been deleted. is this possible? or is there a way round this?

thanks in advance

ok.. yes what you are after is do-able
lets start with the table itself

i would export in xls then change the number in the column A1 = 1
then add 1 etc (in excell)

then import back into an empty table

- now you need a numbering system
do not use auto number - this just give a uniqune number not 1,2,3, etc

theres a couple of ways of doing this - check the samples on this

done it for you

look at the code behind the add new record button
and the table it looks at - this will genreate numbers in order ...
if you thne delete some you can edit the number order etc

this is a rough draft zip file and in 2000

come back if you have a problem with this

ok..

......then import back into an empty table......



hi Gary,

i want to try both suggestions but i'm currently stuck on first. exported to excel and made adjustments.
how do i import back into Access table? it only gives option to export.

make new table -import
table name zxy whatever you can rename it later

or try import should be on access menu

thanks Gary,

i got it eventually, on first import attempt it created an extra column with autonumber but on second attempt i told it not to import original autonumber column. it gets the job done which is the goal.

regarding the numbering file you posted, i don't understand the reason for having two number columns (if you delete a row the number still disappears) but maybe i don't understand the principles or am missing something.

Alex ,

Your table should look lile

autono...id
name
add1etc


what you want is
Autonumberid (Autonumber) (never use this as a numbering system ) but leave it in
now your account/contactnumber starting from1


accountNo (I am using account cos i bill accounts)
now the code i used looks at the nextnumtbl see what the number is and adds 1, then updates the table to this number , so the next time i add a record throught the form it repeats looks at table adds 1 to the cirrent record and then updates the table


if this still thoughs you - make a copy of your d/b empty it and post it up as a zip file - and i'll tweak it (has to be 2000) -

Access can look daunting - but stick with it - understand the concepts and then dive in , what you have asked for really should be a built in module within Access -but isn't -

Access is a great likke d/b and has alsorts of functionality that is pretty easy to grasp , then you want to take it one step further , this is when this site comes into its own , 99.99% of what people want has already been asked - so most code snippets are already here , it understanding the code and then adepting it to your specific useage

the think is, you dont need to interfere with the autonumbers. if you need a different sort order, then simply add a sort key, etc and populate this as required. The autonmuber just ensures that all your records are unique, and can be referenced. Nobody EVER needs to see it