• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • Last Modified:

Autonumber Reset To 1 Without Deleting Current Records

I there a way yo reset the autonumber to 1 without deleting/losing any current data.

0
fatbloater
Asked:
fatbloater
  • 4
  • 4
  • 3
  • +2
4 Solutions
 
DhaestCommented:
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
 
PaurthsCommented:
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
 
PaurthsCommented:
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
fatbloaterAuthor Commented:
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
 
pootle_flumpCommented:
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
 
DhaestCommented:
Then I guess it's impossible.
0
 
pootle_flumpCommented:
Lol - I just don't type fast enough.
0
 
pootle_flumpCommented:
Can't you create a new Long field, update it to the current autonumber values and then follow Dhaest's solution?
0
 
PaurthsCommented:
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
 
dannywarehamCommented:
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
 
dannywarehamCommented:
Sorry Paurths - your post wasn't there when I posted...

:-)
0
 
fatbloaterAuthor Commented:
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
 
fatbloaterAuthor Commented:
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
 
dannywarehamCommented:
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
 
fatbloaterAuthor Commented:
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
 
dannywarehamCommented:
bloater - incrementing the number is the way that i'd go...

:-)
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 4
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now