Autonumber in tables not starting from 0

[IGPCS]

Hi all,

is there a possibility to tell my table from what number i want autonumber to start

what i did till today i created 1000 records and deleted them in order to start 1001


Thanks again

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[fneily]

From my other post:
Let’s say you want the numbering to begin with a different number other then 1. Let’s say 57. Create your table with NO data and no primary key. Have a field called ID and make the data type Number. Save your table. Select your table, right click and select Copy. Then right click and select Paste. Give it a new table name and select Structure Only. Open up this new table and create one record with ID the starting number one less then the one you want, in this case 56. Close this table. Open the first table and now make the ID field an Autonumber type. Close the table. Create an append query from the second table appending the one record to the first table. Now open the first table and add another record. It will automatically be 57. Delete record number 56.

 

[SkipVought]

Hi,

What difference should that make? Create you own Unique ID if necessary.

Skip,

_[red][/red]

 

[genomon]

Found these in The Access Web (thank Dev Ashish):

(Q) How do I set the Autonumber field in my table so that it starts from 1 again?

(A) If your table does not contain any records, simply compacting the database again will
set the Autonumber field back to 1.
Another way would be to delete the AutoNumber field and re-insert it in the table.


(Q) Can I change the starting value of a counter type field?

(A) Yes, you can.. to do this:
Create the first table that contains the counter type field that you want to
start on another number. Don't enter any records.Create a second table, with a
single long integer number type field that has the same name as the counter
field in the first table. Create a record in the second table by entering a
number one less than the number you want to start the counter at in the first table.
Create an append query, to append the one record in the second table to the first table,
and run it Delete the second table, delete the record that you appended to the first table,
and start entering data.

Alternative method:

You can make an Append query that just appends a value from a parameter, to just
the auto-number field (as long as no other fields are required), e.g.:

PARAMETERS [Number] Long;
INSERT INTO tblAuto ( intNumber )
SELECT [Number] AS Expr1;


Outside of a dog, a book is man's best friend. Inside of a dog it's too dark to read.

 

[Remou]

Don't forget the warnings.

[tt]Lilliabeth (TechnicalUser) 3 May 06 14:56
In response to:
I don't get why an autonumber can't have "meaning".

Because if it has meaning, some one will start caring what the values are (like is happening in this thread) and you could spend the rest of your career chasing that rabbit. Want a sequential number that has meaning? Cool. That's sure easy enough to do, so why do it with an autonumber data type where you give up all control?

If you care about the value of the autonumber, you are not using it correctly. It's a handy way to relate tables to each other, and for that, it is it's meaninglessness that is attractive - if it had meaning, some one would decide they want to change it and since it is the field that is common to multiple tables, changing values is pretty scary in a mature database.

See the 7th commandment:

http://www.mvps.org/access/tencommandments.htm

-------------------------------------------------------
JeremyNYC (Programmer) 15 Sep 05 14:47
Keep in mind, though, that there is no reason to reset the autonumber field.
That number should NEVER be relied upon to present any kind of meaningful data.
Autonumbers are guaranteed (more or less) to be unique. They are NOT guaranteed
to be sequential, so the notion of assigning any significance to them is one
that will lead you down a dangerous path. See my write-up here for a bit more
detail:

http://www.abcdataworks.com/id33.htm

From: thread705-1122927[/tt]

 

[IGPCS]

Thanks to all i know this all i wanted to know if ms access has a function that i can set it

thanks for you trying to help

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[Golom]

You can set it

ALTER TABLE myTable ALTER COLUMN myANum Counter(57,23)

Will cause the autonumber field to start at 57 and increment by 23 for each new record

HOWEVER ...

If you do this on an existing table that contains records, those records will not be modified. The next record added will have an autonumber value of 57 and incrementing by 23 thereafter ... and that can cause duplicate autonumbers in the column.

If the table has no records you can then issue the above statement to get it started the way you want. If it doesn't have an autonumber column already then

ALTER TABLE myTable ADD COLUMN myANum Counter(57,23)

will create the new field with the necessary start and increment values.