?
Solved

Autonumber Reset To 1 Without Deleting Current Records

Posted on 2005-04-01
16
Medium Priority
?
428 Views
Last Modified: 2007-11-27
I there a way yo reset the autonumber to 1 without deleting/losing any current data.

0
Comment
Question by:fatbloater
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +2
16 Comments
 
LVL 53

Accepted Solution

by:
Dhaest earned 500 total points
ID: 13679243
If the table is not emtpy then erase the autonumber field from the table design and add it again (with the same name if desired).  If it is set as a primary key, then take off the primary key before erasing it.   When added again put the PK again. The numbers will be reset even that the table is not empty.  
0
 
LVL 12

Assisted Solution

by:Paurths
Paurths earned 500 total points
ID: 13679256
To 1 seems a little impossible... unless there are no records in the table,
then it can be done by simple compacting the database (via menu "extra")
0
 
LVL 12

Expert Comment

by:Paurths
ID: 13679265
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Author Comment

by:fatbloater
ID: 13679266
But wont i then lose the current number?
The current autonumber is part of a reference that identifies each case, i cant lose this data.
Now its a new financial year i need to reset my reference to 1 but keep the old references.
This is not the PK.
0
 
LVL 11

Assisted Solution

by:pootle_flump
pootle_flump earned 500 total points
ID: 13679278
Obvious one but worth stating just in case (forgive me if you have already considered this)-

make sure you aren't orphaning any records or putting yourself in a position where you won't be able to reconcile any information you've previously distributed. Autonumbers remember previously assigned values for good reason.
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 13679282
Then I guess it's impossible.
0
 
LVL 11

Expert Comment

by:pootle_flump
ID: 13679296
Lol - I just don't type fast enough.
0
 
LVL 11

Expert Comment

by:pootle_flump
ID: 13679309
Can't you create a new Long field, update it to the current autonumber values and then follow Dhaest's solution?
0
 
LVL 12

Expert Comment

by:Paurths
ID: 13679330
btw, why would you want to reset the autonumber when it is part of a relationship?
The autonumber should be used in the background, meaningless to a user.
for your invoices (i guess its that...) you should use (add) another field, and use that one. (index it, but double values must be permitted)
0
 
LVL 26

Assisted Solution

by:dannywareham
dannywareham earned 500 total points
ID: 13679430
Autonumber is used to create a unique ID for a record - it should not be used for anything that is useful.meaningful to the user.
What I suggest is that you create an additional field and manipulate that number.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13679433
Sorry Paurths - your post wasn't there when I posted...

:-)
0
 
LVL 1

Author Comment

by:fatbloater
ID: 13679452
The autonumber is used to produce the id, its not the id itself. When someone registers the it looks at the autonumber and coies the number to make th ref.
When i delete it to reset it still puts in values to account for the records that i still have in the table.

0
 
LVL 1

Author Comment

by:fatbloater
ID: 13679474
I just have to re-type that -

The autonumber is used to produce the id, its not the id itself. When someone registers a record it looks at the autonumber and copies the value to make the ref.
When i delete the autonumber to reset it, it still puts in values to account for the records that i still have in the table.

Thats better ;-)>
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13679475
So you're using autonumber (indirectly) to mean something to the user....

You shuold generate an ID a different way, if you need it to mean something.

Autonumber can skip numbers and change - particularly after compacting
0
 
LVL 1

Author Comment

by:fatbloater
ID: 13679505
How would you suggest - there are 8 people using this database.
I tried having a table and adding +1 to it each time, but thought it would be worse -do you think i should go back to this method?
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13679928
bloater - incrementing the number is the way that i'd go...

:-)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question