auto numbers in access

[shelley]

Help! I'm creating a database. My autonumber field needs
to start with a specific autonumber (for example, 73047)
and increase sequentially by 1 each time a new record is
added. Does anyone know how to do this simply (and I do
mean simply!).

 

[Lynn Trapp]

You have to run an insert query and then delete the record you inserted, so
that the next record created will start with the number you need. The SQL of
the query would be as follows:

Insert Into YourTable (YourAutoNumberField)
Values(73046);

However, you must understand that an autonumber field will eventually and
inevitably have gaps in it. If this is acceptable to you, then go forward.
If it is not, you will have to come up with some different kind of way to
increment the field you want.

 

[tpherr]

How is it unreliable for creating sequential, incremental
numbers? That is exactly what is does.

You can set your starting poing and the incremental value.
Default is one but can be whatever increment you decide.

There is a MAX limitation of 4,294,967,295 (4.2B) records
that could then exist in the table before running out of
Autonumber values.

DMAX is a performance hog and a major problem if the
Autonumber filed will be used as a primary or foriegn key.

Tim

-----Original Message-----
Don't even waste your time if you need sequential, incremental
numbers....autonumber is unreliable for that

Use something like =DMAX("[Yourfield]","YourTable")+1
to create your own incrementing ID. This assumes a number datatype...

HTH,

--
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm

You have to run an insert query and then delete the

record you inserted,
so

that the next record created will start with the number

you need. The SQL
of

the query would be as follows:

Insert Into YourTable (YourAutoNumberField)
Values(73046);

However, you must understand that an autonumber field will eventually and
inevitably have gaps in it. If this is acceptable to you, then go forward.
If it is not, you will have to come up with some different kind of way to
increment the field you want.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

.

 

[Kevin3NF]

My personal experience from dozens of databases at differnt locations tells
me that Autonumber sometimes skips numbers. For the most part, it does a
good job. And it is very good for ensuring uniqueness. I have no doubt
that the increment works well, but 100%? Nope.

--
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm

How is it unreliable for creating sequential, incremental
numbers? That is exactly what is does.

You can set your starting poing and the incremental value.
Default is one but can be whatever increment you decide.

There is a MAX limitation of 4,294,967,295 (4.2B) records
that could then exist in the table before running out of
Autonumber values.

DMAX is a performance hog and a major problem if the
Autonumber filed will be used as a primary or foriegn key.

Tim

-----Original Message-----
Don't even waste your time if you need sequential, incremental
numbers....autonumber is unreliable for that

Use something like =DMAX("[Yourfield]","YourTable")+1
to create your own incrementing ID. This assumes a number datatype...

HTH,

--
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm

You have to run an insert query and then delete the

record you inserted,
so

that the next record created will start with the number

you need. The SQL
of

the query would be as follows:

Insert Into YourTable (YourAutoNumberField)
Values(73046);

However, you must understand that an autonumber field will eventually and
inevitably have gaps in it. If this is acceptable to you, then go forward.
If it is not, you will have to come up with some different kind of way to
increment the field you want.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Help! I'm creating a database. My autonumber field needs
to start with a specific autonumber (for example, 73047)
and increase sequentially by 1 each time a new record is
added. Does anyone know how to do this simply (and I do
mean simply!).

.