To Autonumber or not to Autonumber...

I am setting up a job number database for my company. As of now the
"database" is kept in a binder. It will soon be housed on our server. Yay!

The most important field in the database is the job number. It is referenced
by our accounting software, our project management team, upper management,
etc. It is a simple four digit number that is somewhere around 9100 right
now.

What is the best way to generate that number on Access 2007? I have read
about the limitations of the AutoNumber field on multiple forums . Does
anyone have any suggestions on the best way to generate that field?

Thanks in advance for the help!

--
ellsanto

Please do not confuse "Autonumber" with your (meaningful) [JobNumber].

Microsoft Autonumbers are intended to be used as unique row identifiers.
They are not generally fit for human consumption.

If you need a "simple four digit number ... somewhere around 9100...", add a
new field. You can name it [ProjectNumber]. You can get Access to
automatically generate the "next" number in sequence using the DMax()
function (check mvps.org/access or search on line for "custom autonumber").

Regards

Jeff Boyce
Microsoft Office/Access MVP


"ellsanto" wrote in message
...
I am setting up a job number database for my company. As of now the
"database" is kept in a binder. It will soon be housed on our server. Yay!

The most important field in the database is the job number. It is
referenced
by our accounting software, our project management team, upper management,
etc. It is a simple four digit number that is somewhere around 9100 right
now.

What is the best way to generate that number on Access 2007? I have read
about the limitations of the AutoNumber field on multiple forums . Does
anyone have any suggestions on the best way to generate that field?

Thanks in advance for the help!

--
ellsanto

I concur with Jeff,

I always use an autonumber as primary key field and a separate field to
store the meaningfull "human key" field. You can ensure it's unique by
creating an unique index on that field. To generate it I use a numbers
table: see my answer to the topic "Invoice number" in this discussion group.
It gives you more flexibility then using the dmax function.
--
Kind regards
Noëlla


"Jeff Boyce" wrote:

Please do not confuse "Autonumber" with your (meaningful) [JobNumber].

Microsoft Autonumbers are intended to be used as unique row identifiers.
They are not generally fit for human consumption.

If you need a "simple four digit number ... somewhere around 9100...", add a
new field. You can name it [ProjectNumber]. You can get Access to
automatically generate the "next" number in sequence using the DMax()
function (check mvps.org/access or search on line for "custom autonumber").

Regards

Jeff Boyce
Microsoft Office/Access MVP


"ellsanto" wrote in message
...
I am setting up a job number database for my company. As of now the
"database" is kept in a binder. It will soon be housed on our server. Yay!

The most important field in the database is the job number. It is
referenced
by our accounting software, our project management team, upper management,
etc. It is a simple four digit number that is somewhere around 9100 right
now.

What is the best way to generate that number on Access 2007? I have read
about the limitations of the AutoNumber field on multiple forums . Does
anyone have any suggestions on the best way to generate that field?

Thanks in advance for the help!

--
ellsanto

In message ,
ellsanto writes
I am setting up a job number database for my company. As of now the
"database" is kept in a binder. It will soon be housed on our server. Yay!

The most important field in the database is the job number. It is referenced
by our accounting software, our project management team, upper management,
etc. It is a simple four digit number that is somewhere around 9100 right
now.

What is the best way to generate that number on Access 2007? I have read
about the limitations of the AutoNumber field on multiple forums . Does
anyone have any suggestions on the best way to generate that field?

If there is a need to keep the run of numbers contiguous then use a
programmatic generator. If not then an ordinary autonumber field should
be sufficient.


--
Bernard Peek

I strongly disagree with using a table of numbers for this process. Why have
to keep up with such a table and why slow the process with an extra disc
fetch when the DMax function works so well? In addition, such a method could
cause your numbering to get out of sync if any user had a crash at the right
time.

Here is the basic concept:

=Nz(DMax("[InvoiceNumber]","tblInvoice"),0) + 1

The only issue you have to be concerned with is if you are in a multi user
environment, you need to plan for multiple users trying to create a new
invoice at the same time.
--
Dave Hargis, Microsoft Access MVP


"NG" wrote:

I concur with Jeff,

I always use an autonumber as primary key field and a separate field to
store the meaningfull "human key" field. You can ensure it's unique by
creating an unique index on that field. To generate it I use a numbers
table: see my answer to the topic "Invoice number" in this discussion group.
It gives you more flexibility then using the dmax function.
--
Kind regards
Noëlla


"Jeff Boyce" wrote:

Please do not confuse "Autonumber" with your (meaningful) [JobNumber].

Microsoft Autonumbers are intended to be used as unique row identifiers.
They are not generally fit for human consumption.

If you need a "simple four digit number ... somewhere around 9100...", add a
new field. You can name it [ProjectNumber]. You can get Access to
automatically generate the "next" number in sequence using the DMax()
function (check mvps.org/access or search on line for "custom autonumber").

Regards

Jeff Boyce
Microsoft Office/Access MVP


"ellsanto" wrote in message
...
I am setting up a job number database for my company. As of now the
"database" is kept in a binder. It will soon be housed on our server. Yay!

The most important field in the database is the job number. It is
referenced
by our accounting software, our project management team, upper management,
etc. It is a simple four digit number that is somewhere around 9100 right
now.

What is the best way to generate that number on Access 2007? I have read
about the limitations of the AutoNumber field on multiple forums . Does
anyone have any suggestions on the best way to generate that field?

Thanks in advance for the help!

--
ellsanto

Perfect. Thanks, Dave. I modified the line you gave me and inserted it into
the data default value property. Works perfectly. I spent more time than I
want to admit sifting through suggestions from a bunch of sites. Thanks for
the quick easy solution.
--
ellsanto


"Klatuu" wrote:

I strongly disagree with using a table of numbers for this process. Why have
to keep up with such a table and why slow the process with an extra disc
fetch when the DMax function works so well? In addition, such a method could
cause your numbering to get out of sync if any user had a crash at the right
time.

Here is the basic concept:

=Nz(DMax("[InvoiceNumber]","tblInvoice"),0) + 1

The only issue you have to be concerned with is if you are in a multi user
environment, you need to plan for multiple users trying to create a new
invoice at the same time.
--
Dave Hargis, Microsoft Access MVP


"NG" wrote:

I concur with Jeff,

I always use an autonumber as primary key field and a separate field to
store the meaningfull "human key" field. You can ensure it's unique by
creating an unique index on that field. To generate it I use a numbers
table: see my answer to the topic "Invoice number" in this discussion group.
It gives you more flexibility then using the dmax function.
--
Kind regards
Noëlla


"Jeff Boyce" wrote:

Please do not confuse "Autonumber" with your (meaningful) [JobNumber].

Microsoft Autonumbers are intended to be used as unique row identifiers.
They are not generally fit for human consumption.

If you need a "simple four digit number ... somewhere around 9100...", add a
new field. You can name it [ProjectNumber]. You can get Access to
automatically generate the "next" number in sequence using the DMax()
function (check mvps.org/access or search on line for "custom autonumber").

Regards

Jeff Boyce
Microsoft Office/Access MVP


"ellsanto" wrote in message
...
I am setting up a job number database for my company. As of now the
"database" is kept in a binder. It will soon be housed on our server. Yay!

The most important field in the database is the job number. It is
referenced
by our accounting software, our project management team, upper management,
etc. It is a simple four digit number that is somewhere around 9100 right
now.

What is the best way to generate that number on Access 2007? I have read
about the limitations of the AutoNumber field on multiple forums . Does
anyone have any suggestions on the best way to generate that field?

Thanks in advance for the help!

--
ellsanto

Using an autonumber field for a value of any meaning is always a bad idea.
No exceptions.
--
Dave Hargis, Microsoft Access MVP


"Bernard Peek" wrote:

In message ,
ellsanto writes
I am setting up a job number database for my company. As of now the
"database" is kept in a binder. It will soon be housed on our server. Yay!

The most important field in the database is the job number. It is referenced
by our accounting software, our project management team, upper management,
etc. It is a simple four digit number that is somewhere around 9100 right
now.

What is the best way to generate that number on Access 2007? I have read
about the limitations of the AutoNumber field on multiple forums . Does
anyone have any suggestions on the best way to generate that field?

If there is a need to keep the run of numbers contiguous then use a
programmatic generator. If not then an ordinary autonumber field should
be sufficient.


--
Bernard Peek

Klatuu wrote:

I strongly disagree with using a table of numbers for this process. Why have
to keep up with such a table and why slow the process with an extra disc
fetch when the DMax function works so well?

One reason is for a clients welding shop when they ran QC tests.
They wanted to ensure if that if a test was deleted, for whatever
reason, the number was reused. They never wanted to explain to the
client why there were missing numbers.

Yes, accountants would be exceedingly upset with this.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/

Youu next question is whether or not to use your job number as a PK. My
advice for your situation is no. Your "deleted job" is a good example of why
not. You will have had 2 different job records in existence with the same
job number,

That is an entirely different circumstance, Tony.
Even for that, I would probably not use a table of available numbers. I
would probably use a boolean field to denote the record as deleted and when I
wanted a new record to use a previously used number, I would do a DMin
filtered for only deleted records.
--
Dave Hargis, Microsoft Access MVP


"Tony Toews [MVP]" wrote:

Klatuu wrote:

I strongly disagree with using a table of numbers for this process. Why have
to keep up with such a table and why slow the process with an extra disc
fetch when the DMax function works so well?

One reason is for a clients welding shop when they ran QC tests.
They wanted to ensure if that if a test was deleted, for whatever
reason, the number was reused. They never wanted to explain to the
client why there were missing numbers.

Yes, accountants would be exceedingly upset with this.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/