Help: Resetting Autonumber

[Walshie1987]

Hi,

I need to set my autonumber to start at 10000. I've done this once already by doing an append query (duplicate table structure, add 1 record and number to start from etc, then making append query) and it worked fine.

I have taken the same steps again to redo this and it still carries on from the last number it issued me. Any ideas?

Thanks
Chris

 

[PHV]

As you asked in this forum, an SQL answer:
ALTER TABLE yourTable ALTER COLUMN yourAutoNumber COUNTER(10000,1);

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

You appear to have fallen in to the trap of thinking that autonumbers mean something: they do not. Do not use autonumber for incremental numbers, it will not work, as you have found out. An autonumber can only be depended on to be unique, after that it may be very large, jump about, or be negative. If you want a number for the user, build one and keep autonumber for the programme/r.

 

[Walshie1987]

I see what you mean, What it is, I work in Purchasing and obviously place orders (using me database to print off, log orders, save orders etc)I want to now set the database up to generate me a uniqe number for each order starting from 10000, so autonumber seems quite appropriate.

But, as I've been doing a few tests on the db I am now on number 10019 and want to reset it back to 10000 just so a few months down the line I don't think "Where are orders 10000 - 100018?"

Any easy ways of doing this?

Cheers

 

[PHV]

Any easy ways of doing this
Have you tried my suggested action query ?

 

[Walshie1987]

Yeah i must have entered something wrong, That works fine now, Thanks alot!

 

[lespaul]

but the point above should be heeded....autonumbers should not mean anything....missing sequential numbers in an autonumber field is normal behavior...if you need a true sequential number, add a field to your table and increment another way.

HTH


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Walshie1987]

Thanks for that, just out of curiosity, how do you increment another way? Don't need too much detail as as long as I am getting a totally unique number that'll work fine, but won't mind knowing for future reference

Thanks again
Chris

 

[fneily]

Here's one way:
To create your own autonumber field, create a table with a field called ID(or whatever). Create a form for that table. On the form, go to design view and click on the text box of the ID field, and open the properties sheet. Click on Default value and enter:
=Dmax(“[ID]”,”tablename”)+1
Now when you go to the next new record, it will be incremented by 1.